payments/migration1: freeze core payment code

diff --git a/payments/db/migration1/codec.go b/payments/db/migration1/codec.go
new file mode 100644
index 0000000..acc173b
--- /dev/null
+++ b/payments/db/migration1/codec.go
@@ -0,0 +1,198 @@
+package migration1
+
+import (
+	"encoding/binary"
+	"fmt"
+	"io"
+	"time"
+
+	"github.com/btcsuite/btcd/wire"
+	"github.com/lightningnetwork/lnd/lnwire"
+)
+
+// Big endian is the preferred byte order, due to cursor scans over
+// integer keys iterating in order.
+var byteOrder = binary.BigEndian
+
+// UnknownElementType is an error returned when the codec is unable to encode
+// or decode a particular type.
+//
+// NOTE: This is a frozen, self-contained copy of the serialization error type
+// to ensure that this migration package has no dependency on live packages
+// whose implementation may change after this migration was released.
+type UnknownElementType struct {
+	method  string
+	element interface{}
+}
+
+// NewUnknownElementType creates a new UnknownElementType error from the passed
+// method name and element.
+func NewUnknownElementType(method string, el interface{}) UnknownElementType {
+	return UnknownElementType{method: method, element: el}
+}
+
+// Error returns the name of the method that encountered the error, as well as
+// the type that was unsupported.
+func (e UnknownElementType) Error() string {
+	return fmt.Sprintf("Unknown type in %s: %T", e.method, e.element)
+}
+
+// WriteElement serializes a single element into the provided io.Writer using
+// big-endian byte order. Only the types required by the migration1 package are
+// supported. This is a frozen, self-contained copy of the serialization logic
+// to ensure that this migration package has no dependency on live packages
+// whose implementation may change after this migration was released.
+func WriteElement(w io.Writer, element interface{}) error {
+	switch e := element.(type) {
+	case paymentIndexType:
+		if err := binary.Write(w, byteOrder, e); err != nil {
+			return err
+		}
+
+	case uint8:
+		if err := binary.Write(w, byteOrder, e); err != nil {
+			return err
+		}
+
+	case uint32:
+		if err := binary.Write(w, byteOrder, e); err != nil {
+			return err
+		}
+
+	case uint64:
+		if err := binary.Write(w, byteOrder, e); err != nil {
+			return err
+		}
+
+	case lnwire.MilliSatoshi:
+		if err := binary.Write(w, byteOrder, uint64(e)); err != nil {
+			return err
+		}
+
+	case [32]byte:
+		if _, err := w.Write(e[:]); err != nil {
+			return err
+		}
+
+	case []byte:
+		if err := wire.WriteVarBytes(w, 0, e); err != nil {
+			return err
+		}
+
+	default:
+		return UnknownElementType{"WriteElement", e}
+	}
+
+	return nil
+}
+
+// WriteElements serializes a variadic list of elements into the given
+// io.Writer.
+func WriteElements(w io.Writer, elements ...interface{}) error {
+	for _, element := range elements {
+		if err := WriteElement(w, element); err != nil {
+			return err
+		}
+	}
+
+	return nil
+}
+
+// ReadElement deserializes a single element from the provided io.Reader using
+// big-endian byte order. Only the types required by the migration1 package are
+// supported. This is a frozen, self-contained copy of the deserialization
+// logic to ensure that this migration package has no dependency on live
+// packages whose implementation may change after this migration was released.
+func ReadElement(r io.Reader, element interface{}) error {
+	switch e := element.(type) {
+	case *paymentIndexType:
+		if err := binary.Read(r, byteOrder, e); err != nil {
+			return err
+		}
+
+	case *uint8:
+		if err := binary.Read(r, byteOrder, e); err != nil {
+			return err
+		}
+
+	case *uint32:
+		if err := binary.Read(r, byteOrder, e); err != nil {
+			return err
+		}
+
+	case *uint64:
+		if err := binary.Read(r, byteOrder, e); err != nil {
+			return err
+		}
+
+	case *lnwire.MilliSatoshi:
+		var a uint64
+		if err := binary.Read(r, byteOrder, &a); err != nil {
+			return err
+		}
+		*e = lnwire.MilliSatoshi(a)
+
+	case *[32]byte:
+		if _, err := io.ReadFull(r, e[:]); err != nil {
+			return err
+		}
+
+	case *[]byte:
+		b, err := wire.ReadVarBytes(r, 0, 66000, "[]byte")
+		if err != nil {
+			return err
+		}
+		*e = b
+
+	default:
+		return UnknownElementType{"ReadElement", e}
+	}
+
+	return nil
+}
+
+// ReadElements deserializes the provided io.Reader into a variadic list of
+// target elements.
+func ReadElements(r io.Reader, elements ...interface{}) error {
+	for _, element := range elements {
+		if err := ReadElement(r, element); err != nil {
+			return err
+		}
+	}
+
+	return nil
+}
+
+// deserializeTime deserializes time as unix nanoseconds.
+func deserializeTime(r io.Reader) (time.Time, error) {
+	var scratch [8]byte
+	if _, err := io.ReadFull(r, scratch[:]); err != nil {
+		return time.Time{}, err
+	}
+
+	// Convert to time.Time. Interpret unix nano time zero as a zero
+	// time.Time value.
+	unixNano := byteOrder.Uint64(scratch[:])
+	if unixNano == 0 {
+		return time.Time{}, nil
+	}
+
+	return time.Unix(0, int64(unixNano)), nil
+}
+
+// serializeTime serializes time as unix nanoseconds.
+func serializeTime(w io.Writer, t time.Time) error {
+	var scratch [8]byte
+
+	// Convert to unix nano seconds, but only if time is non-zero. Calling
+	// UnixNano() on a zero time yields an undefined result.
+	var unixNano int64
+	if !t.IsZero() {
+		unixNano = t.UnixNano()
+	}
+
+	byteOrder.PutUint64(scratch[:], uint64(unixNano))
+	_, err := w.Write(scratch[:])
+
+	return err
+}
diff --git a/payments/db/migration1/errors.go b/payments/db/migration1/errors.go
new file mode 100644
index 0000000..7c2c982
--- /dev/null
+++ b/payments/db/migration1/errors.go
@@ -0,0 +1,154 @@
+package migration1
+
+import "errors"
+
+var (
+	// ErrAlreadyPaid signals we have already paid this payment hash.
+	ErrAlreadyPaid = errors.New("invoice is already paid")
+
+	// ErrPaymentInFlight signals that payment for this payment hash is
+	// already "in flight" on the network.
+	ErrPaymentInFlight = errors.New("payment is in transition")
+
+	// ErrPaymentExists is returned when we try to initialize an already
+	// existing payment that is not failed.
+	ErrPaymentExists = errors.New("payment already exists")
+
+	// ErrPaymentInternal is returned when performing the payment has a
+	// conflicting state, such as,
+	// - payment has StatusSucceeded but remaining amount is not zero.
+	// - payment has StatusInitiated but remaining amount is zero.
+	// - payment has StatusFailed but remaining amount is zero.
+	ErrPaymentInternal = errors.New("internal error")
+
+	// ErrPaymentNotInitiated is returned if the payment wasn't initiated.
+	ErrPaymentNotInitiated = errors.New("payment isn't initiated")
+
+	// ErrPaymentAlreadySucceeded is returned in the event we attempt to
+	// change the status of a payment already succeeded.
+	ErrPaymentAlreadySucceeded = errors.New("payment is already succeeded")
+
+	// ErrPaymentAlreadyFailed is returned in the event we attempt to alter
+	// a failed payment.
+	ErrPaymentAlreadyFailed = errors.New("payment has already failed")
+
+	// ErrUnknownPaymentStatus is returned when we do not recognize the
+	// existing state of a payment.
+	ErrUnknownPaymentStatus = errors.New("unknown payment status")
+
+	// ErrPaymentTerminal is returned if we attempt to alter a payment that
+	// already has reached a terminal condition.
+	ErrPaymentTerminal = errors.New("payment has reached terminal " +
+		"condition")
+
+	// ErrAttemptAlreadySettled is returned if we try to alter an already
+	// settled HTLC attempt.
+	ErrAttemptAlreadySettled = errors.New("attempt already settled")
+
+	// ErrAttemptAlreadyFailed is returned if we try to alter an already
+	// failed HTLC attempt.
+	ErrAttemptAlreadyFailed = errors.New("attempt already failed")
+
+	// ErrValueMismatch is returned if we try to register a non-MPP attempt
+	// with an amount that doesn't match the payment amount.
+	ErrValueMismatch = errors.New("attempted value doesn't match payment " +
+		"amount")
+
+	// ErrValueExceedsAmt is returned if we try to register an attempt that
+	// would take the total sent amount above the payment amount.
+	ErrValueExceedsAmt = errors.New("attempted value exceeds payment " +
+		"amount")
+
+	// ErrNonMPPayment is returned if we try to register an MPP attempt for
+	// a payment that already has a non-MPP attempt registered.
+	ErrNonMPPayment = errors.New("payment has non-MPP attempts")
+
+	// ErrMPPayment is returned if we try to register a non-MPP attempt for
+	// a payment that already has an MPP attempt registered.
+	ErrMPPayment = errors.New("payment has MPP attempts")
+
+	// ErrMPPRecordInBlindedPayment is returned if we try to register an
+	// attempt with an MPP record for a payment to a blinded path.
+	ErrMPPRecordInBlindedPayment = errors.New("blinded payment cannot " +
+		"contain MPP records")
+
+	// ErrBlindedPaymentTotalAmountMismatch is returned if we try to
+	// register an HTLC shard to a blinded route where the total amount
+	// doesn't match existing shards.
+	ErrBlindedPaymentTotalAmountMismatch = errors.New("blinded path " +
+		"total amount mismatch")
+
+	// ErrMixedBlindedAndNonBlindedPayments is returned if we try to
+	// register a non-blinded attempt to a payment which uses a blinded
+	// paths or vice versa.
+	ErrMixedBlindedAndNonBlindedPayments = errors.New("mixed blinded and " +
+		"non-blinded payments")
+
+	// ErrBlindedPaymentMissingTotalAmount is returned if we try to
+	// register a blinded payment attempt where the final hop doesn't set
+	// the total amount.
+	ErrBlindedPaymentMissingTotalAmount = errors.New("blinded payment " +
+		"final hop must set total amount")
+
+	// ErrMPPPaymentAddrMismatch is returned if we try to register an MPP
+	// shard where the payment address doesn't match existing shards.
+	ErrMPPPaymentAddrMismatch = errors.New("payment address mismatch")
+
+	// ErrMPPTotalAmountMismatch is returned if we try to register an MPP
+	// shard where the total amount doesn't match existing shards.
+	ErrMPPTotalAmountMismatch = errors.New("mp payment total amount " +
+		"mismatch")
+
+	// ErrPaymentPendingSettled is returned when we try to add a new
+	// attempt to a payment that has at least one of its HTLCs settled.
+	ErrPaymentPendingSettled = errors.New("payment has settled htlcs")
+
+	// ErrPaymentPendingFailed is returned when we try to add a new attempt
+	// to a payment that already has a failure reason.
+	ErrPaymentPendingFailed = errors.New("payment has failure reason")
+
+	// ErrSentExceedsTotal is returned if the payment's current total sent
+	// amount exceed the total amount.
+	ErrSentExceedsTotal = errors.New("total sent exceeds total amount")
+
+	// ErrNoAttemptInfo is returned when no attempt info is stored yet.
+	ErrNoAttemptInfo = errors.New("unable to find attempt info for " +
+		"inflight payment")
+)
+
+// KV backend specific errors.
+var (
+	// ErrNoSequenceNumber is returned if we look up a payment which does
+	// not have a sequence number.
+	ErrNoSequenceNumber = errors.New("sequence number not found")
+
+	// ErrDuplicateNotFound is returned when we lookup a payment by its
+	// index and cannot find a payment with a matching sequence number.
+	ErrDuplicateNotFound = errors.New("duplicate payment not found")
+
+	// ErrNoDuplicateBucket is returned when we expect to find duplicates
+	// when looking up a payment from its index, but the payment does not
+	// have any.
+	ErrNoDuplicateBucket = errors.New("expected duplicate bucket")
+
+	// ErrNoDuplicateNestedBucket is returned if we do not find duplicate
+	// payments in their own sub-bucket.
+	ErrNoDuplicateNestedBucket = errors.New("nested duplicate bucket not " +
+		"found")
+
+	// ErrNoDuplicateSequenceNumber is returned when a duplicate payment
+	// sub-bucket does not contain the sequence number key.
+	ErrNoDuplicateSequenceNumber = errors.New("duplicate payment " +
+		"sequence number not found")
+
+	// ErrNoSequenceNrIndex is returned when an attempt to lookup a payment
+	// index is made for a sequence number that is not indexed.
+	//
+	// NOTE: Only used for the kv backend.
+	ErrNoSequenceNrIndex = errors.New("payment sequence number index " +
+		"does not exist")
+
+	// errMaxPaymentsReached is used internally to signal that the maximum
+	// number of payments has been reached during a paginated query.
+	errMaxPaymentsReached = errors.New("max payments reached")
+)
diff --git a/payments/db/migration1/interface.go b/payments/db/migration1/interface.go
new file mode 100644
index 0000000..7d47118
--- /dev/null
+++ b/payments/db/migration1/interface.go
@@ -0,0 +1,140 @@
+package migration1
+
+import (
+	"context"
+
+	"github.com/lightningnetwork/lnd/lntypes"
+)
+
+// DB represents the interface to the underlying payments database.
+type DB interface {
+	PaymentReader
+	PaymentWriter
+}
+
+// PaymentReader represents the interface to read operations from the payments
+// database.
+type PaymentReader interface {
+	// QueryPayments queries the payments database and should support
+	// pagination.
+	QueryPayments(ctx context.Context, query Query) (Response, error)
+
+	// FetchPayment fetches the payment corresponding to the given payment
+	// hash.
+	FetchPayment(ctx context.Context,
+		paymentHash lntypes.Hash) (*MPPayment, error)
+
+	// FetchInFlightPayments returns all payments with status InFlight.
+	FetchInFlightPayments(ctx context.Context) ([]*MPPayment, error)
+}
+
+// PaymentWriter represents the interface to write operations to the payments
+// database.
+type PaymentWriter interface {
+	// DeletePayment deletes a payment from the DB given its payment hash.
+	DeletePayment(ctx context.Context, paymentHash lntypes.Hash,
+		failedAttemptsOnly bool) error
+
+	// DeletePayments deletes all payments from the DB given the specified
+	// flags.
+	DeletePayments(ctx context.Context, failedOnly,
+		failedAttemptsOnly bool) (int, error)
+
+	PaymentControl
+}
+
+// PaymentControl represents the interface to control the payment lifecycle and
+// its database operations. This interface represents the control flow of how
+// a payment should be handled in the database. They are not just writing
+// operations but they inherently represent the flow of a payment. The methods
+// are called in the following order.
+//
+// 1. InitPayment.
+// 2. RegisterAttempt (a payment can have multiple attempts).
+// 3. SettleAttempt or FailAttempt (attempts can also fail as long as the
+// sending amount will be eventually settled).
+// 4. Payment succeeds or "Fail" is called.
+// 5. DeleteFailedAttempts is called which will delete all failed attempts
+// for a payment to clean up the database.
+type PaymentControl interface {
+	// InitPayment checks that no other payment with the same payment hash
+	// exists in the database before creating a new payment. However, it
+	// should allow the user making a subsequent payment if the payment is
+	// in a Failed state.
+	InitPayment(context.Context, lntypes.Hash, *PaymentCreationInfo) error
+
+	// RegisterAttempt atomically records the provided HTLCAttemptInfo.
+	//
+	// IMPORTANT: Callers MUST serialize calls to RegisterAttempt for the
+	// same payment hash. Concurrent calls will result in race conditions
+	// where both calls read the same initial payment state, validate
+	// against stale data, and could cause overpayment. For example:
+	//   - Both goroutines fetch payment with 400 sats sent
+	//   - Both validate sending 650 sats won't overpay (within limit)
+	//   - Both commit successfully
+	//   - Result: 1700 sats sent, exceeding the payment amount
+	// The payment router/controller layer is responsible for ensuring
+	// serialized access per payment hash.
+	RegisterAttempt(context.Context, lntypes.Hash,
+		*HTLCAttemptInfo) (*MPPayment, error)
+
+	// SettleAttempt marks the given attempt settled with the preimage. If
+	// this is a multi shard payment, this might implicitly mean the
+	// full payment succeeded.
+	//
+	// After invoking this method, InitPayment should always return an
+	// error to prevent us from making duplicate payments to the same
+	// payment hash. The provided preimage is atomically saved to the DB
+	// for record keeping.
+	SettleAttempt(context.Context, lntypes.Hash, uint64,
+		*HTLCSettleInfo) (*MPPayment, error)
+
+	// FailAttempt marks the given payment attempt failed.
+	FailAttempt(context.Context, lntypes.Hash, uint64,
+		*HTLCFailInfo) (*MPPayment, error)
+
+	// Fail transitions a payment into the Failed state, and records
+	// the ultimate reason the payment failed. Note that this should only
+	// be called when all active attempts are already failed. After
+	// invoking this method, InitPayment should return nil on its next call
+	// for this payment hash, allowing the user to make a subsequent
+	// payment.
+	Fail(context.Context, lntypes.Hash, FailureReason) (*MPPayment, error)
+
+	// DeleteFailedAttempts removes all failed HTLCs from the db. It should
+	// be called for a given payment whenever all inflight htlcs are
+	// completed, and the payment has reached a final terminal state.
+	DeleteFailedAttempts(context.Context, lntypes.Hash) error
+}
+
+// DBMPPayment is an interface that represents the payment state during a
+// payment lifecycle.
+type DBMPPayment interface {
+	// GetState returns the current state of the payment.
+	GetState() *MPPaymentState
+
+	// Terminated returns true if the payment is in a final state.
+	Terminated() bool
+
+	// GetStatus returns the current status of the payment.
+	GetStatus() PaymentStatus
+
+	// NeedWaitAttempts specifies whether the payment needs to wait for the
+	// outcome of an attempt.
+	NeedWaitAttempts() (bool, error)
+
+	// GetHTLCs returns all HTLCs of this payment.
+	GetHTLCs() []HTLCAttempt
+
+	// InFlightHTLCs returns all HTLCs that are in flight.
+	InFlightHTLCs() []HTLCAttempt
+
+	// AllowMoreAttempts is used to decide whether we can safely attempt
+	// more HTLCs for a given payment state. Return an error if the payment
+	// is in an unexpected state.
+	AllowMoreAttempts() (bool, error)
+
+	// TerminalInfo returns the settled HTLC attempt or the payment's
+	// failure reason.
+	TerminalInfo() (*HTLCAttempt, *FailureReason)
+}
diff --git a/payments/db/migration1/kv_duplicate_payments.go b/payments/db/migration1/kv_duplicate_payments.go
new file mode 100644
index 0000000..f72848d
--- /dev/null
+++ b/payments/db/migration1/kv_duplicate_payments.go
@@ -0,0 +1,249 @@
+package migration1
+
+import (
+	"bytes"
+	"encoding/binary"
+	"fmt"
+	"io"
+	"time"
+
+	"github.com/btcsuite/btcd/btcec/v2"
+	"github.com/lightningnetwork/lnd/kvdb"
+	"github.com/lightningnetwork/lnd/lntypes"
+	"github.com/lightningnetwork/lnd/lnwire"
+)
+
+var (
+	// duplicatePaymentsBucket is the name of a optional sub-bucket within
+	// the payment hash bucket, that is used to hold duplicate payments to a
+	// payment hash. This is needed to support information from earlier
+	// versions of lnd, where it was possible to pay to a payment hash more
+	// than once.
+	duplicatePaymentsBucket = []byte("payment-duplicate-bucket")
+
+	// duplicatePaymentSettleInfoKey is a key used in the payment's
+	// sub-bucket to store the settle info of the payment.
+	duplicatePaymentSettleInfoKey = []byte("payment-settle-info")
+
+	// duplicatePaymentAttemptInfoKey is a key used in the payment's
+	// sub-bucket to store the info about the latest attempt that was done
+	// for the payment in question.
+	duplicatePaymentAttemptInfoKey = []byte("payment-attempt-info")
+
+	// duplicatePaymentCreationInfoKey is a key used in the payment's
+	// sub-bucket to store the creation info of the payment.
+	duplicatePaymentCreationInfoKey = []byte("payment-creation-info")
+
+	// duplicatePaymentFailInfoKey is a key used in the payment's sub-bucket
+	// to store information about the reason a payment failed.
+	duplicatePaymentFailInfoKey = []byte("payment-fail-info")
+
+	// duplicatePaymentSequenceKey is a key used in the payment's sub-bucket
+	// to store the sequence number of the payment.
+	duplicatePaymentSequenceKey = []byte("payment-sequence-key")
+)
+
+// duplicateHTLCAttemptInfo contains static information about a specific HTLC
+// attempt for a payment. This information is used by the router to handle any
+// errors coming back after an attempt is made, and to query the switch about
+// the status of the attempt.
+type duplicateHTLCAttemptInfo struct {
+	// attemptID is the unique ID used for this attempt.
+	attemptID uint64
+
+	// sessionKey is the ephemeral key used for this attempt.
+	sessionKey [btcec.PrivKeyBytesLen]byte
+
+	// route is the route attempted to send the HTLC.
+	route Route
+}
+
+// fetchDuplicatePaymentStatus fetches the payment status of the payment. If
+// the payment isn't found, it will return error `ErrPaymentNotInitiated`.
+func fetchDuplicatePaymentStatus(bucket kvdb.RBucket) (PaymentStatus, error) {
+	if bucket.Get(duplicatePaymentSettleInfoKey) != nil {
+		return StatusSucceeded, nil
+	}
+
+	if bucket.Get(duplicatePaymentFailInfoKey) != nil {
+		return StatusFailed, nil
+	}
+
+	if bucket.Get(duplicatePaymentCreationInfoKey) != nil {
+		return StatusInFlight, nil
+	}
+
+	return 0, ErrPaymentNotInitiated
+}
+
+func deserializeDuplicateHTLCAttemptInfo(r io.Reader) (
+	*duplicateHTLCAttemptInfo, error) {
+
+	a := &duplicateHTLCAttemptInfo{}
+	err := ReadElements(r, &a.attemptID, &a.sessionKey)
+	if err != nil {
+		return nil, err
+	}
+	a.route, err = DeserializeRoute(r)
+	if err != nil {
+		return nil, err
+	}
+
+	return a, nil
+}
+
+func deserializeDuplicatePaymentCreationInfo(r io.Reader) (
+	*PaymentCreationInfo, error) {
+
+	var scratch [8]byte
+
+	c := &PaymentCreationInfo{}
+
+	if _, err := io.ReadFull(r, c.PaymentIdentifier[:]); err != nil {
+		return nil, err
+	}
+
+	if _, err := io.ReadFull(r, scratch[:]); err != nil {
+		return nil, err
+	}
+	c.Value = lnwire.MilliSatoshi(byteOrder.Uint64(scratch[:]))
+
+	if _, err := io.ReadFull(r, scratch[:]); err != nil {
+		return nil, err
+	}
+	c.CreationTime = time.Unix(int64(byteOrder.Uint64(scratch[:])), 0)
+
+	if _, err := io.ReadFull(r, scratch[:4]); err != nil {
+		return nil, err
+	}
+
+	reqLen := byteOrder.Uint32(scratch[:4])
+	payReq := make([]byte, reqLen)
+	if reqLen > 0 {
+		if _, err := io.ReadFull(r, payReq); err != nil {
+			return nil, err
+		}
+	}
+	c.PaymentRequest = payReq
+
+	return c, nil
+}
+
+func fetchDuplicatePayment(bucket kvdb.RBucket) (*MPPayment, error) {
+	seqBytes := bucket.Get(duplicatePaymentSequenceKey)
+	if seqBytes == nil {
+		return nil, fmt.Errorf("sequence number not found")
+	}
+
+	sequenceNum := binary.BigEndian.Uint64(seqBytes)
+
+	// Get the payment status.
+	paymentStatus, err := fetchDuplicatePaymentStatus(bucket)
+	if err != nil {
+		return nil, err
+	}
+
+	// Get the PaymentCreationInfo.
+	b := bucket.Get(duplicatePaymentCreationInfoKey)
+	if b == nil {
+		return nil, fmt.Errorf("creation info not found")
+	}
+
+	r := bytes.NewReader(b)
+	creationInfo, err := deserializeDuplicatePaymentCreationInfo(r)
+	if err != nil {
+		return nil, err
+	}
+
+	// Get failure reason if available.
+	var failureReason *FailureReason
+	b = bucket.Get(duplicatePaymentFailInfoKey)
+	if b != nil {
+		reason := FailureReason(b[0])
+		failureReason = &reason
+	}
+
+	payment := &MPPayment{
+		SequenceNum:   sequenceNum,
+		Info:          creationInfo,
+		FailureReason: failureReason,
+		Status:        paymentStatus,
+	}
+
+	// Get the HTLCAttemptInfo. It can be absent.
+	b = bucket.Get(duplicatePaymentAttemptInfoKey)
+	if b != nil {
+		r = bytes.NewReader(b)
+		attempt, err := deserializeDuplicateHTLCAttemptInfo(r)
+		if err != nil {
+			return nil, err
+		}
+
+		htlc := HTLCAttempt{
+			HTLCAttemptInfo: HTLCAttemptInfo{
+				AttemptID:  attempt.attemptID,
+				Route:      attempt.route,
+				sessionKey: attempt.sessionKey,
+			},
+		}
+
+		// Get the payment preimage. This is only found for
+		// successful payments.
+		b = bucket.Get(duplicatePaymentSettleInfoKey)
+		if b != nil {
+			var preimg lntypes.Preimage
+			copy(preimg[:], b)
+
+			htlc.Settle = &HTLCSettleInfo{
+				Preimage:   preimg,
+				SettleTime: time.Time{},
+			}
+		} else {
+			// Otherwise the payment must have failed.
+			htlc.Failure = &HTLCFailInfo{
+				FailTime: time.Time{},
+			}
+		}
+
+		payment.HTLCs = []HTLCAttempt{htlc}
+	}
+
+	return payment, nil
+}
+
+func fetchDuplicatePayments(paymentHashBucket kvdb.RBucket) ([]*MPPayment,
+	error) {
+
+	var payments []*MPPayment
+
+	// For older versions of lnd, duplicate payments to a payment has was
+	// possible. These will be found in a sub-bucket indexed by their
+	// sequence number if available.
+	dup := paymentHashBucket.NestedReadBucket(duplicatePaymentsBucket)
+	if dup == nil {
+		return nil, nil
+	}
+
+	err := dup.ForEach(func(k, v []byte) error {
+		subBucket := dup.NestedReadBucket(k)
+		if subBucket == nil {
+			// We one bucket for each duplicate to be found.
+			return fmt.Errorf("non bucket element" +
+				"in duplicate bucket")
+		}
+
+		p, err := fetchDuplicatePayment(subBucket)
+		if err != nil {
+			return err
+		}
+
+		payments = append(payments, p)
+
+		return nil
+	})
+	if err != nil {
+		return nil, err
+	}
+
+	return payments, nil
+}
diff --git a/payments/db/migration1/kv_store.go b/payments/db/migration1/kv_store.go
new file mode 100644
index 0000000..51bf005
--- /dev/null
+++ b/payments/db/migration1/kv_store.go
@@ -0,0 +1,2120 @@
+package migration1
+
+import (
+	"bytes"
+	"context"
+	"encoding/binary"
+	"errors"
+	"fmt"
+	"io"
+	"math"
+	"sort"
+	"sync"
+	"time"
+
+	"github.com/btcsuite/btcd/btcec/v2"
+	"github.com/btcsuite/btcd/wire"
+	"github.com/lightningnetwork/lnd/channeldb"
+	"github.com/lightningnetwork/lnd/kvdb"
+	"github.com/lightningnetwork/lnd/lntypes"
+	"github.com/lightningnetwork/lnd/lnwire"
+	"github.com/lightningnetwork/lnd/record"
+	"github.com/lightningnetwork/lnd/tlv"
+)
+
+const (
+	// paymentSeqBlockSize is the block size used when we batch allocate
+	// payment sequences for future payments.
+	paymentSeqBlockSize = 1000
+
+	// paymentProgressLogInterval is the interval we use limiting the
+	// logging output of payment processing.
+	paymentProgressLogInterval = 30 * time.Second
+)
+
+//nolint:ll
+var (
+	// paymentsRootBucket is the name of the top-level bucket within the
+	// database that stores all data related to payments. Within this
+	// bucket, each payment hash its own sub-bucket keyed by its payment
+	// hash.
+	//
+	// Bucket hierarchy:
+	//
+	// root-bucket
+	//      |
+	//      |-- <paymenthash>
+	//      |        |--sequence-key: <sequence number>
+	//      |        |--creation-info-key: <creation info>
+	//      |        |--fail-info-key: <(optional) fail info>
+	//      |        |
+	//      |        |--payment-htlcs-bucket (shard-bucket)
+	//      |        |        |
+	//      |        |        |-- ai<htlc attempt ID>: <htlc attempt info>
+	//      |        |        |-- si<htlc attempt ID>: <(optional) settle info>
+	//      |        |        |-- fi<htlc attempt ID>: <(optional) fail info>
+	//      |        |        |
+	//      |        |       ...
+	//      |        |
+	//      |        |
+	//      |        |--duplicate-bucket (only for old, completed payments)
+	//      |                 |
+	//      |                 |-- <seq-num>
+	//      |                 |       |--sequence-key: <sequence number>
+	//      |                 |       |--creation-info-key: <creation info>
+	//      |                 |       |--ai: <attempt info>
+	//      |                 |       |--si: <settle info>
+	//      |                 |       |--fi: <fail info>
+	//      |                 |
+	//      |                 |-- <seq-num>
+	//      |                 |       |
+	//      |                ...     ...
+	//      |
+	//      |-- <paymenthash>
+	//      |        |
+	//      |       ...
+	//     ...
+	//
+	paymentsRootBucket = []byte("payments-root-bucket")
+
+	// paymentSequenceKey is a key used in the payment's sub-bucket to
+	// store the sequence number of the payment.
+	paymentSequenceKey = []byte("payment-sequence-key")
+
+	// paymentCreationInfoKey is a key used in the payment's sub-bucket to
+	// store the creation info of the payment.
+	paymentCreationInfoKey = []byte("payment-creation-info")
+
+	// paymentHtlcsBucket is a bucket where we'll store the information
+	// about the HTLCs that were attempted for a payment.
+	paymentHtlcsBucket = []byte("payment-htlcs-bucket")
+
+	// htlcAttemptInfoKey is the key used as the prefix of an HTLC attempt
+	// to store the info about the attempt that was done for the HTLC in
+	// question. The HTLC attempt ID is concatenated at the end.
+	htlcAttemptInfoKey = []byte("ai")
+
+	// htlcSettleInfoKey is the key used as the prefix of an HTLC attempt
+	// settle info, if any. The HTLC attempt ID is concatenated at the end.
+	htlcSettleInfoKey = []byte("si")
+
+	// htlcFailInfoKey is the key used as the prefix of an HTLC attempt
+	// failure information, if any.The  HTLC attempt ID is concatenated at
+	// the end.
+	htlcFailInfoKey = []byte("fi")
+
+	// paymentFailInfoKey is a key used in the payment's sub-bucket to
+	// store information about the reason a payment failed.
+	paymentFailInfoKey = []byte("payment-fail-info")
+
+	// paymentsIndexBucket is the name of the top-level bucket within the
+	// database that stores an index of payment sequence numbers to its
+	// payment hash.
+	// payments-sequence-index-bucket
+	// 	|--<sequence-number>: <payment hash>
+	// 	|--...
+	// 	|--<sequence-number>: <payment hash>
+	paymentsIndexBucket = []byte("payments-index-bucket")
+)
+
+// KVStore implements persistence for payments and payment attempts.
+type KVStore struct {
+	// Sequence management for the kv store.
+	seqMu     sync.Mutex
+	currSeq   uint64
+	storedSeq uint64
+
+	// db is the underlying database implementation.
+	db kvdb.Backend
+}
+
+// A compile-time constraint to ensure KVStore implements DB.
+var _ DB = (*KVStore)(nil)
+
+// NewKVStore creates a new KVStore for payments.
+func NewKVStore(db kvdb.Backend,
+	options ...OptionModifier) (*KVStore, error) {
+
+	opts := DefaultOptions()
+	for _, applyOption := range options {
+		applyOption(opts)
+	}
+
+	if !opts.NoMigration {
+		if err := initKVStore(db); err != nil {
+			return nil, err
+		}
+	}
+
+	return &KVStore{
+		db: db,
+	}, nil
+}
+
+// paymentsTopLevelBuckets is a list of top-level buckets that are used for
+// the payments database when using the kv store.
+var paymentsTopLevelBuckets = [][]byte{
+	paymentsRootBucket,
+	paymentsIndexBucket,
+}
+
+// initKVStore creates and initializes the top-level buckets for the payment db.
+func initKVStore(db kvdb.Backend) error {
+	err := kvdb.Update(db, func(tx kvdb.RwTx) error {
+		for _, tlb := range paymentsTopLevelBuckets {
+			if _, err := tx.CreateTopLevelBucket(tlb); err != nil {
+				return err
+			}
+		}
+
+		return nil
+	}, func() {})
+	if err != nil {
+		return fmt.Errorf("unable to create new payments db: %w", err)
+	}
+
+	return nil
+}
+
+// InitPayment checks or records the given PaymentCreationInfo with the DB,
+// making sure it does not already exist as an in-flight payment. When this
+// method returns successfully, the payment is guaranteed to be in the InFlight
+// state.
+func (p *KVStore) InitPayment(_ context.Context, paymentHash lntypes.Hash,
+	info *PaymentCreationInfo) error {
+
+	// Obtain a new sequence number for this payment. This is used
+	// to sort the payments in order of creation, and also acts as
+	// a unique identifier for each payment.
+	sequenceNum, err := p.nextPaymentSequence()
+	if err != nil {
+		return err
+	}
+
+	var b bytes.Buffer
+	if err := serializePaymentCreationInfo(&b, info); err != nil {
+		return err
+	}
+	infoBytes := b.Bytes()
+
+	var updateErr error
+	err = kvdb.Batch(p.db, func(tx kvdb.RwTx) error {
+		// Reset the update error, to avoid carrying over an error
+		// from a previous execution of the batched db transaction.
+		updateErr = nil
+
+		prefetchPayment(tx, paymentHash)
+		bucket, err := createPaymentBucket(tx, paymentHash)
+		if err != nil {
+			return err
+		}
+
+		// Get the existing status of this payment, if any.
+		paymentStatus, err := fetchPaymentStatus(bucket)
+
+		switch {
+		// If no error is returned, it means we already have this
+		// payment. We'll check the status to decide whether we allow
+		// retrying the payment or return a specific error.
+		case err == nil:
+			if err := paymentStatus.initializable(); err != nil {
+				updateErr = err
+				return nil
+			}
+
+		// Otherwise, if the error is not `ErrPaymentNotInitiated`,
+		// we'll return the error.
+		case !errors.Is(err, ErrPaymentNotInitiated):
+			return err
+		}
+
+		// Before we set our new sequence number, we check whether this
+		// payment has a previously set sequence number and remove its
+		// index entry if it exists. This happens in the case where we
+		// have a previously attempted payment which was left in a state
+		// where we can retry.
+		seqBytes := bucket.Get(paymentSequenceKey)
+		if seqBytes != nil {
+			indexBucket := tx.ReadWriteBucket(paymentsIndexBucket)
+			if err := indexBucket.Delete(seqBytes); err != nil {
+				return err
+			}
+		}
+
+		// Once we have obtained a sequence number, we add an entry
+		// to our index bucket which will map the sequence number to
+		// our payment identifier.
+		err = createPaymentIndexEntry(
+			tx, sequenceNum, info.PaymentIdentifier,
+		)
+		if err != nil {
+			return err
+		}
+
+		err = bucket.Put(paymentSequenceKey, sequenceNum)
+		if err != nil {
+			return err
+		}
+
+		// Add the payment info to the bucket, which contains the
+		// static information for this payment
+		err = bucket.Put(paymentCreationInfoKey, infoBytes)
+		if err != nil {
+			return err
+		}
+
+		// We'll delete any lingering HTLCs to start with, in case we
+		// are initializing a payment that was attempted earlier, but
+		// left in a state where we could retry.
+		err = bucket.DeleteNestedBucket(paymentHtlcsBucket)
+		if err != nil && !errors.Is(err, kvdb.ErrBucketNotFound) {
+			return err
+		}
+
+		// Also delete any lingering failure info now that we are
+		// re-attempting.
+		return bucket.Delete(paymentFailInfoKey)
+	})
+	if err != nil {
+		return fmt.Errorf("unable to init payment: %w", err)
+	}
+
+	return updateErr
+}
+
+// DeleteFailedAttempts deletes all failed htlcs for a payment.
+func (p *KVStore) DeleteFailedAttempts(ctx context.Context,
+	hash lntypes.Hash) error {
+
+	const failedHtlcsOnly = true
+	err := p.DeletePayment(ctx, hash, failedHtlcsOnly)
+	if err != nil {
+		return err
+	}
+
+	return nil
+}
+
+// paymentIndexTypeHash is a payment index type which indicates that we have
+// created an index of payment sequence number to payment hash.
+type paymentIndexType uint8
+
+// paymentIndexTypeHash is a payment index type which indicates that we have
+// created an index of payment sequence number to payment hash.
+const paymentIndexTypeHash paymentIndexType = 0
+
+// createPaymentIndexEntry creates a payment hash typed index for a payment. The
+// index produced contains a payment index type (which can be used in future to
+// signal different payment index types) and the payment identifier.
+func createPaymentIndexEntry(tx kvdb.RwTx, sequenceNumber []byte,
+	id lntypes.Hash) error {
+
+	var b bytes.Buffer
+	if err := WriteElements(&b, paymentIndexTypeHash, id[:]); err != nil {
+		return err
+	}
+
+	indexes := tx.ReadWriteBucket(paymentsIndexBucket)
+
+	return indexes.Put(sequenceNumber, b.Bytes())
+}
+
+// deserializePaymentIndex deserializes a payment index entry. This function
+// currently only supports deserialization of payment hash indexes, and will
+// fail for other types.
+func deserializePaymentIndex(r io.Reader) (lntypes.Hash, error) {
+	var (
+		indexType   paymentIndexType
+		paymentHash []byte
+	)
+
+	if err := ReadElements(r, &indexType, &paymentHash); err != nil {
+		return lntypes.Hash{}, err
+	}
+
+	// While we only have on payment index type, we do not need to use our
+	// index type to deserialize the index. However, we sanity check that
+	// this type is as expected, since we had to read it out anyway.
+	if indexType != paymentIndexTypeHash {
+		return lntypes.Hash{}, fmt.Errorf("unknown payment index "+
+			"type: %v", indexType)
+	}
+
+	hash, err := lntypes.MakeHash(paymentHash)
+	if err != nil {
+		return lntypes.Hash{}, err
+	}
+
+	return hash, nil
+}
+
+// RegisterAttempt atomically records the provided HTLCAttemptInfo to the
+// DB.
+func (p *KVStore) RegisterAttempt(_ context.Context, paymentHash lntypes.Hash,
+	attempt *HTLCAttemptInfo) (*MPPayment, error) {
+
+	// Serialize the information before opening the db transaction.
+	var a bytes.Buffer
+	err := serializeHTLCAttemptInfo(&a, attempt)
+	if err != nil {
+		return nil, err
+	}
+	htlcInfoBytes := a.Bytes()
+
+	htlcIDBytes := make([]byte, 8)
+	binary.BigEndian.PutUint64(htlcIDBytes, attempt.AttemptID)
+
+	var payment *MPPayment
+	err = kvdb.Batch(p.db, func(tx kvdb.RwTx) error {
+		prefetchPayment(tx, paymentHash)
+		bucket, err := fetchPaymentBucketUpdate(tx, paymentHash)
+		if err != nil {
+			return err
+		}
+
+		payment, err = fetchPayment(bucket)
+		if err != nil {
+			return err
+		}
+
+		// Check if registering a new attempt is allowed.
+		if err := payment.Registrable(); err != nil {
+			return err
+		}
+
+		// Verify the attempt is compatible with the existing payment.
+		if err := verifyAttempt(payment, attempt); err != nil {
+			return err
+		}
+
+		htlcsBucket, err := bucket.CreateBucketIfNotExists(
+			paymentHtlcsBucket,
+		)
+		if err != nil {
+			return err
+		}
+
+		err = htlcsBucket.Put(
+			htlcBucketKey(htlcAttemptInfoKey, htlcIDBytes),
+			htlcInfoBytes,
+		)
+		if err != nil {
+			return err
+		}
+
+		// Retrieve attempt info for the notification.
+		payment, err = fetchPayment(bucket)
+
+		return err
+	})
+	if err != nil {
+		return nil, err
+	}
+
+	return payment, err
+}
+
+// SettleAttempt marks the given attempt settled with the preimage. If this is
+// a multi shard payment, this might implicitly mean that the full payment
+// succeeded.
+//
+// After invoking this method, InitPayment should always return an error to
+// prevent us from making duplicate payments to the same payment hash. The
+// provided preimage is atomically saved to the DB for record keeping.
+func (p *KVStore) SettleAttempt(_ context.Context, hash lntypes.Hash,
+	attemptID uint64, settleInfo *HTLCSettleInfo) (*MPPayment, error) {
+
+	var b bytes.Buffer
+	if err := serializeHTLCSettleInfo(&b, settleInfo); err != nil {
+		return nil, err
+	}
+	settleBytes := b.Bytes()
+
+	return p.updateHtlcKey(hash, attemptID, htlcSettleInfoKey, settleBytes)
+}
+
+// FailAttempt marks the given payment attempt failed.
+func (p *KVStore) FailAttempt(_ context.Context, hash lntypes.Hash,
+	attemptID uint64, failInfo *HTLCFailInfo) (*MPPayment, error) {
+
+	var b bytes.Buffer
+	if err := serializeHTLCFailInfo(&b, failInfo); err != nil {
+		return nil, err
+	}
+	failBytes := b.Bytes()
+
+	return p.updateHtlcKey(hash, attemptID, htlcFailInfoKey, failBytes)
+}
+
+// updateHtlcKey updates a database key for the specified htlc.
+func (p *KVStore) updateHtlcKey(paymentHash lntypes.Hash,
+	attemptID uint64, key, value []byte) (*MPPayment, error) {
+
+	aid := make([]byte, 8)
+	binary.BigEndian.PutUint64(aid, attemptID)
+
+	var payment *MPPayment
+	err := kvdb.Batch(p.db, func(tx kvdb.RwTx) error {
+		payment = nil
+
+		prefetchPayment(tx, paymentHash)
+		bucket, err := fetchPaymentBucketUpdate(tx, paymentHash)
+		if err != nil {
+			return err
+		}
+
+		p, err := fetchPayment(bucket)
+		if err != nil {
+			return err
+		}
+
+		// We can only update keys of in-flight payments. We allow
+		// updating keys even if the payment has reached a terminal
+		// condition, since the HTLC outcomes must still be updated.
+		if err := p.Status.updatable(); err != nil {
+			return err
+		}
+
+		htlcsBucket := bucket.NestedReadWriteBucket(paymentHtlcsBucket)
+		if htlcsBucket == nil {
+			return fmt.Errorf("htlcs bucket not found")
+		}
+
+		attemptKey := htlcBucketKey(htlcAttemptInfoKey, aid)
+		if htlcsBucket.Get(attemptKey) == nil {
+			return fmt.Errorf("HTLC with ID %v not registered",
+				attemptID)
+		}
+
+		// Make sure the shard is not already failed or settled.
+		failKey := htlcBucketKey(htlcFailInfoKey, aid)
+		if htlcsBucket.Get(failKey) != nil {
+			return ErrAttemptAlreadyFailed
+		}
+
+		settleKey := htlcBucketKey(htlcSettleInfoKey, aid)
+		if htlcsBucket.Get(settleKey) != nil {
+			return ErrAttemptAlreadySettled
+		}
+
+		// Add or update the key for this htlc.
+		err = htlcsBucket.Put(htlcBucketKey(key, aid), value)
+		if err != nil {
+			return err
+		}
+
+		// Retrieve attempt info for the notification.
+		payment, err = fetchPayment(bucket)
+
+		return err
+	})
+	if err != nil {
+		return nil, err
+	}
+
+	return payment, err
+}
+
+// Fail transitions a payment into the Failed state, and records the reason the
+// payment failed. After invoking this method, InitPayment should return nil on
+// its next call for this payment hash, allowing the switch to make a
+// subsequent payment.
+func (p *KVStore) Fail(_ context.Context, paymentHash lntypes.Hash,
+	reason FailureReason) (*MPPayment, error) {
+
+	var (
+		updateErr error
+		payment   *MPPayment
+	)
+	err := kvdb.Batch(p.db, func(tx kvdb.RwTx) error {
+		// Reset the update error, to avoid carrying over an error
+		// from a previous execution of the batched db transaction.
+		updateErr = nil
+		payment = nil
+
+		prefetchPayment(tx, paymentHash)
+		bucket, err := fetchPaymentBucketUpdate(tx, paymentHash)
+		if errors.Is(err, ErrPaymentNotInitiated) {
+			updateErr = ErrPaymentNotInitiated
+			return nil
+		} else if err != nil {
+			return err
+		}
+
+		// We mark the payment as failed as long as it is known. This
+		// lets the last attempt to fail with a terminal write its
+		// failure to the KVStore without synchronizing with
+		// other attempts.
+		_, err = fetchPaymentStatus(bucket)
+		if errors.Is(err, ErrPaymentNotInitiated) {
+			updateErr = ErrPaymentNotInitiated
+			return nil
+		} else if err != nil {
+			return err
+		}
+
+		// Put the failure reason in the bucket for record keeping.
+		v := []byte{byte(reason)}
+		err = bucket.Put(paymentFailInfoKey, v)
+		if err != nil {
+			return err
+		}
+
+		// Retrieve attempt info for the notification, if available.
+		payment, err = fetchPayment(bucket)
+		if err != nil {
+			return err
+		}
+
+		return nil
+	})
+	if err != nil {
+		return nil, err
+	}
+
+	return payment, updateErr
+}
+
+// FetchPayment returns information about a payment from the database.
+func (p *KVStore) FetchPayment(_ context.Context,
+	paymentHash lntypes.Hash) (*MPPayment, error) {
+
+	var payment *MPPayment
+	err := kvdb.View(p.db, func(tx kvdb.RTx) error {
+		prefetchPayment(tx, paymentHash)
+		bucket, err := fetchPaymentBucket(tx, paymentHash)
+		if err != nil {
+			return err
+		}
+
+		payment, err = fetchPayment(bucket)
+
+		return err
+	}, func() {
+		payment = nil
+	})
+	if err != nil {
+		return nil, err
+	}
+
+	return payment, nil
+}
+
+// prefetchPayment attempts to prefetch as much of the payment as possible to
+// reduce DB roundtrips.
+func prefetchPayment(tx kvdb.RTx, paymentHash lntypes.Hash) {
+	rb := kvdb.RootBucket(tx)
+	kvdb.Prefetch(
+		rb,
+		[]string{
+			// Prefetch all keys in the payment's bucket.
+			string(paymentsRootBucket),
+			string(paymentHash[:]),
+		},
+		[]string{
+			// Prefetch all keys in the payment's htlc bucket.
+			string(paymentsRootBucket),
+			string(paymentHash[:]),
+			string(paymentHtlcsBucket),
+		},
+	)
+}
+
+// createPaymentBucket creates or fetches the sub-bucket assigned to this
+// payment hash.
+func createPaymentBucket(tx kvdb.RwTx, paymentHash lntypes.Hash) (
+	kvdb.RwBucket, error) {
+
+	payments, err := tx.CreateTopLevelBucket(paymentsRootBucket)
+	if err != nil {
+		return nil, err
+	}
+
+	return payments.CreateBucketIfNotExists(paymentHash[:])
+}
+
+// fetchPaymentBucket fetches the sub-bucket assigned to this payment hash. If
+// the bucket does not exist, it returns ErrPaymentNotInitiated.
+func fetchPaymentBucket(tx kvdb.RTx, paymentHash lntypes.Hash) (
+	kvdb.RBucket, error) {
+
+	payments := tx.ReadBucket(paymentsRootBucket)
+	if payments == nil {
+		return nil, ErrPaymentNotInitiated
+	}
+
+	bucket := payments.NestedReadBucket(paymentHash[:])
+	if bucket == nil {
+		return nil, ErrPaymentNotInitiated
+	}
+
+	return bucket, nil
+}
+
+// fetchPaymentBucketUpdate is identical to fetchPaymentBucket, but it returns a
+// bucket that can be written to.
+func fetchPaymentBucketUpdate(tx kvdb.RwTx, paymentHash lntypes.Hash) (
+	kvdb.RwBucket, error) {
+
+	payments := tx.ReadWriteBucket(paymentsRootBucket)
+	if payments == nil {
+		return nil, ErrPaymentNotInitiated
+	}
+
+	bucket := payments.NestedReadWriteBucket(paymentHash[:])
+	if bucket == nil {
+		return nil, ErrPaymentNotInitiated
+	}
+
+	return bucket, nil
+}
+
+// nextPaymentSequence returns the next sequence number to store for a new
+// payment.
+func (p *KVStore) nextPaymentSequence() ([]byte, error) {
+	p.seqMu.Lock()
+	defer p.seqMu.Unlock()
+
+	// Set a new upper bound in the DB every 1000 payments to avoid
+	// conflicts on the sequence when using etcd.
+	if p.currSeq == p.storedSeq {
+		var currPaymentSeq, newUpperBound uint64
+		if err := kvdb.Update(p.db, func(tx kvdb.RwTx) error {
+			paymentsBucket, err := tx.CreateTopLevelBucket(
+				paymentsRootBucket,
+			)
+			if err != nil {
+				return err
+			}
+
+			currPaymentSeq = paymentsBucket.Sequence()
+			newUpperBound = currPaymentSeq + paymentSeqBlockSize
+
+			return paymentsBucket.SetSequence(newUpperBound)
+		}, func() {}); err != nil {
+			return nil, err
+		}
+
+		// We lazy initialize the cached currPaymentSeq here using the
+		// first nextPaymentSequence() call. This if statement will auto
+		// initialize our stored currPaymentSeq, since by default both
+		// this variable and storedPaymentSeq are zero which in turn
+		// will have us fetch the current values from the DB.
+		if p.currSeq == 0 {
+			p.currSeq = currPaymentSeq
+		}
+
+		p.storedSeq = newUpperBound
+	}
+
+	p.currSeq++
+	b := make([]byte, 8)
+	binary.BigEndian.PutUint64(b, p.currSeq)
+
+	return b, nil
+}
+
+// fetchPaymentStatus fetches the payment status of the payment. If the payment
+// isn't found, it will return error `ErrPaymentNotInitiated`.
+func fetchPaymentStatus(bucket kvdb.RBucket) (PaymentStatus, error) {
+	// Creation info should be set for all payments, regardless of state.
+	// If not, it is unknown.
+	if bucket.Get(paymentCreationInfoKey) == nil {
+		return 0, ErrPaymentNotInitiated
+	}
+
+	payment, err := fetchPayment(bucket)
+	if err != nil {
+		return 0, err
+	}
+
+	return payment.Status, nil
+}
+
+// FetchInFlightPayments returns all payments with status InFlight.
+func (p *KVStore) FetchInFlightPayments(_ context.Context) ([]*MPPayment,
+	error) {
+
+	var (
+		inFlights      []*MPPayment
+		start          = time.Now()
+		lastLogTime    = time.Now()
+		processedCount int
+	)
+
+	err := kvdb.View(p.db, func(tx kvdb.RTx) error {
+		payments := tx.ReadBucket(paymentsRootBucket)
+		if payments == nil {
+			return nil
+		}
+
+		return payments.ForEach(func(k, _ []byte) error {
+			bucket := payments.NestedReadBucket(k)
+			if bucket == nil {
+				return fmt.Errorf("non bucket element")
+			}
+
+			p, err := fetchPayment(bucket)
+			if err != nil {
+				return err
+			}
+
+			processedCount++
+			if time.Since(lastLogTime) >=
+				paymentProgressLogInterval {
+
+				log.Debugf("Scanning inflight payments "+
+					"(in progress), processed %d, last "+
+					"processed payment: %v", processedCount,
+					p.Info)
+
+				lastLogTime = time.Now()
+			}
+
+			// Skip the payment if it's terminated.
+			if p.Terminated() {
+				return nil
+			}
+
+			inFlights = append(inFlights, p)
+
+			return nil
+		})
+	}, func() {
+		inFlights = nil
+	})
+	if err != nil {
+		return nil, err
+	}
+
+	elapsed := time.Since(start)
+	log.Debugf("Completed scanning for inflight payments: "+
+		"total_processed=%d, found_inflight=%d, elapsed=%v",
+		processedCount, len(inFlights),
+		elapsed.Round(time.Millisecond))
+
+	return inFlights, nil
+}
+
+// htlcBucketKey creates a composite key from prefix and id where the result is
+// simply the two concatenated.
+func htlcBucketKey(prefix, id []byte) []byte {
+	key := make([]byte, len(prefix)+len(id))
+	copy(key, prefix)
+	copy(key[len(prefix):], id)
+
+	return key
+}
+
+// FetchPayments returns all sent payments found in the DB.
+func (p *KVStore) FetchPayments() ([]*MPPayment, error) {
+	var payments []*MPPayment
+
+	err := kvdb.View(p.db, func(tx kvdb.RTx) error {
+		paymentsBucket := tx.ReadBucket(paymentsRootBucket)
+		if paymentsBucket == nil {
+			return nil
+		}
+
+		return paymentsBucket.ForEach(func(k, v []byte) error {
+			bucket := paymentsBucket.NestedReadBucket(k)
+			if bucket == nil {
+				// We only expect sub-buckets to be found in
+				// this top-level bucket.
+				return fmt.Errorf("non bucket element in " +
+					"payments bucket")
+			}
+
+			p, err := fetchPayment(bucket)
+			if err != nil {
+				return err
+			}
+
+			payments = append(payments, p)
+
+			// For older versions of lnd, duplicate payments to a
+			// payment has was possible. These will be found in a
+			// sub-bucket indexed by their sequence number if
+			// available.
+			duplicatePayments, err := fetchDuplicatePayments(bucket)
+			if err != nil {
+				return err
+			}
+
+			payments = append(payments, duplicatePayments...)
+
+			return nil
+		})
+	}, func() {
+		payments = nil
+	})
+	if err != nil {
+		return nil, err
+	}
+
+	// Before returning, sort the payments by their sequence number.
+	sort.Slice(payments, func(i, j int) bool {
+		return payments[i].SequenceNum < payments[j].SequenceNum
+	})
+
+	return payments, nil
+}
+
+func fetchCreationInfo(bucket kvdb.RBucket) (*PaymentCreationInfo, error) {
+	b := bucket.Get(paymentCreationInfoKey)
+	if b == nil {
+		return nil, fmt.Errorf("creation info not found")
+	}
+
+	r := bytes.NewReader(b)
+
+	return deserializePaymentCreationInfo(r)
+}
+
+func fetchPayment(bucket kvdb.RBucket) (*MPPayment, error) {
+	seqBytes := bucket.Get(paymentSequenceKey)
+	if seqBytes == nil {
+		return nil, fmt.Errorf("sequence number not found")
+	}
+
+	sequenceNum := binary.BigEndian.Uint64(seqBytes)
+
+	// Get the PaymentCreationInfo.
+	creationInfo, err := fetchCreationInfo(bucket)
+	if err != nil {
+		return nil, err
+	}
+
+	var htlcs []HTLCAttempt
+	htlcsBucket := bucket.NestedReadBucket(paymentHtlcsBucket)
+	if htlcsBucket != nil {
+		// Get the payment attempts. This can be empty.
+		htlcs, err = fetchHtlcAttempts(htlcsBucket)
+		if err != nil {
+			return nil, err
+		}
+	}
+
+	// Get failure reason if available.
+	var failureReason *FailureReason
+	b := bucket.Get(paymentFailInfoKey)
+	if b != nil {
+		reason := FailureReason(b[0])
+		failureReason = &reason
+	}
+
+	// Create a new payment.
+	payment := &MPPayment{
+		SequenceNum:   sequenceNum,
+		Info:          creationInfo,
+		HTLCs:         htlcs,
+		FailureReason: failureReason,
+	}
+
+	// Set its state and status.
+	if err := payment.setState(); err != nil {
+		return nil, err
+	}
+
+	return payment, nil
+}
+
+// fetchHtlcAttempts retrieves all htlc attempts made for the payment found in
+// the given bucket.
+func fetchHtlcAttempts(bucket kvdb.RBucket) ([]HTLCAttempt, error) {
+	htlcsMap := make(map[uint64]*HTLCAttempt)
+
+	attemptInfoCount := 0
+	err := bucket.ForEach(func(k, v []byte) error {
+		aid := byteOrder.Uint64(k[len(k)-8:])
+
+		if _, ok := htlcsMap[aid]; !ok {
+			htlcsMap[aid] = &HTLCAttempt{}
+		}
+
+		var err error
+		switch {
+		case bytes.HasPrefix(k, htlcAttemptInfoKey):
+			attemptInfo, err := readHtlcAttemptInfo(v)
+			if err != nil {
+				return err
+			}
+
+			attemptInfo.AttemptID = aid
+			htlcsMap[aid].HTLCAttemptInfo = *attemptInfo
+			attemptInfoCount++
+
+		case bytes.HasPrefix(k, htlcSettleInfoKey):
+			htlcsMap[aid].Settle, err = readHtlcSettleInfo(v)
+			if err != nil {
+				return err
+			}
+
+		case bytes.HasPrefix(k, htlcFailInfoKey):
+			htlcsMap[aid].Failure, err = readHtlcFailInfo(v)
+			if err != nil {
+				return err
+			}
+
+		default:
+			return fmt.Errorf("unknown htlc attempt key")
+		}
+
+		return nil
+	})
+	if err != nil {
+		return nil, err
+	}
+
+	// Sanity check that all htlcs have an attempt info.
+	if attemptInfoCount != len(htlcsMap) {
+		return nil, ErrNoAttemptInfo
+	}
+
+	keys := make([]uint64, len(htlcsMap))
+	i := 0
+	for k := range htlcsMap {
+		keys[i] = k
+		i++
+	}
+
+	// Sort HTLC attempts by their attempt ID. This is needed because in the
+	// DB we store the attempts with keys prefixed by their status which
+	// changes order (groups them together by status).
+	sort.Slice(keys, func(i, j int) bool {
+		return keys[i] < keys[j]
+	})
+
+	htlcs := make([]HTLCAttempt, len(htlcsMap))
+	for i, key := range keys {
+		htlcs[i] = *htlcsMap[key]
+	}
+
+	return htlcs, nil
+}
+
+// readHtlcAttemptInfo reads the payment attempt info for this htlc.
+func readHtlcAttemptInfo(b []byte) (*HTLCAttemptInfo, error) {
+	r := bytes.NewReader(b)
+	return deserializeHTLCAttemptInfo(r)
+}
+
+// readHtlcSettleInfo reads the settle info for the htlc. If the htlc isn't
+// settled, nil is returned.
+func readHtlcSettleInfo(b []byte) (*HTLCSettleInfo, error) {
+	r := bytes.NewReader(b)
+	return deserializeHTLCSettleInfo(r)
+}
+
+// readHtlcFailInfo reads the failure info for the htlc. If the htlc hasn't
+// failed, nil is returned.
+func readHtlcFailInfo(b []byte) (*HTLCFailInfo, error) {
+	r := bytes.NewReader(b)
+	return deserializeHTLCFailInfo(r)
+}
+
+// fetchFailedHtlcKeys retrieves the bucket keys of all failed HTLCs of a
+// payment bucket.
+func fetchFailedHtlcKeys(bucket kvdb.RBucket) ([][]byte, error) {
+	htlcsBucket := bucket.NestedReadBucket(paymentHtlcsBucket)
+
+	var htlcs []HTLCAttempt
+	var err error
+	if htlcsBucket != nil {
+		htlcs, err = fetchHtlcAttempts(htlcsBucket)
+		if err != nil {
+			return nil, err
+		}
+	}
+
+	// Now iterate though them and save the bucket keys for the failed
+	// HTLCs.
+	var htlcKeys [][]byte
+	for _, h := range htlcs {
+		if h.Failure == nil {
+			continue
+		}
+
+		htlcKeyBytes := make([]byte, 8)
+		binary.BigEndian.PutUint64(htlcKeyBytes, h.AttemptID)
+
+		htlcKeys = append(htlcKeys, htlcKeyBytes)
+	}
+
+	return htlcKeys, nil
+}
+
+// QueryPayments is a query to the payments database which is restricted
+// to a subset of payments by the payments query, containing an offset
+// index and a maximum number of returned payments.
+func (p *KVStore) QueryPayments(_ context.Context,
+	query Query) (Response, error) {
+
+	var resp Response
+
+	if err := kvdb.View(p.db, func(tx kvdb.RTx) error {
+		// Get the root payments bucket.
+		paymentsBucket := tx.ReadBucket(paymentsRootBucket)
+		if paymentsBucket == nil {
+			return nil
+		}
+
+		// Get the index bucket which maps sequence number -> payment
+		// hash and duplicate bool. If we have a payments bucket, we
+		// should have an indexes bucket as well.
+		indexes := tx.ReadBucket(paymentsIndexBucket)
+		if indexes == nil {
+			return fmt.Errorf("index bucket does not exist")
+		}
+
+		// accumulatePayments gets payments with the sequence number
+		// and hash provided and adds them to our list of payments if
+		// they meet the criteria of our query. It returns the number
+		// of payments that were added.
+		accumulatePayments := func(sequenceKey, hash []byte) (bool,
+			error) {
+
+			r := bytes.NewReader(hash)
+			paymentHash, err := deserializePaymentIndex(r)
+			if err != nil {
+				return false, err
+			}
+
+			payment, err := fetchPaymentWithSequenceNumber(
+				tx, paymentHash, sequenceKey,
+			)
+			if err != nil {
+				return false, err
+			}
+
+			// To keep compatibility with the old API, we only
+			// return non-succeeded payments if requested.
+			if payment.Status != StatusSucceeded &&
+				!query.IncludeIncomplete {
+
+				return false, err
+			}
+
+			// Get the creation time in Unix seconds, this always
+			// rounds down the nanoseconds to full seconds.
+			createTime := payment.Info.CreationTime.Unix()
+
+			// Skip any payments that were created before the
+			// specified time.
+			if createTime < query.CreationDateStart {
+				return false, nil
+			}
+
+			// Skip any payments that were created after the
+			// specified time.
+			if query.CreationDateEnd != 0 &&
+				createTime > query.CreationDateEnd {
+
+				return false, nil
+			}
+
+			// At this point, we've exhausted the offset, so we'll
+			// begin collecting invoices found within the range.
+			resp.Payments = append(resp.Payments, payment)
+
+			return true, nil
+		}
+
+		// Create a paginator which reads from our sequence index bucket
+		// with the parameters provided by the payments query.
+		paginator := channeldb.NewPaginator(
+			indexes.ReadCursor(), query.Reversed, query.IndexOffset,
+			query.MaxPayments,
+		)
+
+		// Run a paginated query, adding payments to our response.
+		if err := paginator.Query(accumulatePayments); err != nil {
+			return err
+		}
+
+		// Counting the total number of payments is expensive, since we
+		// literally have to traverse the cursor linearly, which can
+		// take quite a while. So it's an optional query parameter.
+		if query.CountTotal {
+			var (
+				totalPayments uint64
+				err           error
+			)
+			countFn := func(_, _ []byte) error {
+				totalPayments++
+
+				return nil
+			}
+
+			// In non-boltdb database backends, there's a faster
+			// ForAll query that allows for batch fetching items.
+			fastBucket, ok := indexes.(kvdb.ExtendedRBucket)
+			if ok {
+				err = fastBucket.ForAll(countFn)
+			} else {
+				err = indexes.ForEach(countFn)
+			}
+			if err != nil {
+				return fmt.Errorf("error counting payments: %w",
+					err)
+			}
+
+			resp.TotalCount = totalPayments
+		}
+
+		return nil
+	}, func() {
+		resp = Response{}
+	}); err != nil {
+		return resp, err
+	}
+
+	// Need to swap the payments slice order if reversed order.
+	if query.Reversed {
+		for l, r := 0, len(resp.Payments)-1; l < r; l, r = l+1, r-1 {
+			resp.Payments[l], resp.Payments[r] =
+				resp.Payments[r], resp.Payments[l]
+		}
+	}
+
+	// Set the first and last index of the returned payments so that the
+	// caller can resume from this point later on.
+	if len(resp.Payments) > 0 {
+		resp.FirstIndexOffset = resp.Payments[0].SequenceNum
+		resp.LastIndexOffset =
+			resp.Payments[len(resp.Payments)-1].SequenceNum
+	}
+
+	return resp, nil
+}
+
+// fetchPaymentWithSequenceNumber get the payment which matches the payment hash
+// *and* sequence number provided from the database. This is required because
+// we previously had more than one payment per hash, so we have multiple indexes
+// pointing to a single payment; we want to retrieve the correct one.
+func fetchPaymentWithSequenceNumber(tx kvdb.RTx, paymentHash lntypes.Hash,
+	sequenceNumber []byte) (*MPPayment, error) {
+
+	// We can now lookup the payment keyed by its hash in
+	// the payments root bucket.
+	bucket, err := fetchPaymentBucket(tx, paymentHash)
+	if err != nil {
+		return nil, err
+	}
+
+	// A single payment hash can have multiple payments associated with it.
+	// We lookup our sequence number first, to determine whether this is
+	// the payment we are actually looking for.
+	seqBytes := bucket.Get(paymentSequenceKey)
+	if seqBytes == nil {
+		return nil, ErrNoSequenceNumber
+	}
+
+	// If this top level payment has the sequence number we are looking for,
+	// return it.
+	if bytes.Equal(seqBytes, sequenceNumber) {
+		return fetchPayment(bucket)
+	}
+
+	// If we were not looking for the top level payment, we are looking for
+	// one of our duplicate payments. We need to iterate through the seq
+	// numbers in this bucket to find the correct payments. If we do not
+	// find a duplicate payments bucket here, something is wrong.
+	dup := bucket.NestedReadBucket(duplicatePaymentsBucket)
+	if dup == nil {
+		return nil, ErrNoDuplicateBucket
+	}
+
+	var duplicatePayment *MPPayment
+	err = dup.ForEach(func(k, v []byte) error {
+		subBucket := dup.NestedReadBucket(k)
+		if subBucket == nil {
+			// We one bucket for each duplicate to be found.
+			return ErrNoDuplicateNestedBucket
+		}
+
+		seqBytes := subBucket.Get(duplicatePaymentSequenceKey)
+		if seqBytes == nil {
+			return ErrNoDuplicateSequenceNumber
+		}
+
+		// If this duplicate payment is not the sequence number we are
+		// looking for, we can continue.
+		if !bytes.Equal(seqBytes, sequenceNumber) {
+			return nil
+		}
+
+		duplicatePayment, err = fetchDuplicatePayment(subBucket)
+		if err != nil {
+			return err
+		}
+
+		return nil
+	})
+	if err != nil {
+		return nil, err
+	}
+
+	// If none of the duplicate payments matched our sequence number, we
+	// failed to find the payment with this sequence number; something is
+	// wrong.
+	if duplicatePayment == nil {
+		return nil, ErrDuplicateNotFound
+	}
+
+	return duplicatePayment, nil
+}
+
+// DeletePayment deletes a payment from the DB given its payment hash. If
+// failedHtlcsOnly is set, only failed HTLC attempts of the payment will be
+// deleted.
+func (p *KVStore) DeletePayment(_ context.Context, paymentHash lntypes.Hash,
+	failedHtlcsOnly bool) error {
+
+	return kvdb.Update(p.db, func(tx kvdb.RwTx) error {
+		payments := tx.ReadWriteBucket(paymentsRootBucket)
+		if payments == nil {
+			return nil
+		}
+
+		bucket := payments.NestedReadWriteBucket(paymentHash[:])
+		if bucket == nil {
+			return fmt.Errorf("non bucket element in payments " +
+				"bucket")
+		}
+
+		// If the status is InFlight, we cannot safely delete
+		// the payment information, so we return early.
+		paymentStatus, err := fetchPaymentStatus(bucket)
+		if err != nil {
+			return err
+		}
+
+		// If the payment has inflight HTLCs, we cannot safely delete
+		// the payment information, so we return an error.
+		if err := paymentStatus.removable(); err != nil {
+			return fmt.Errorf("payment '%v' has inflight HTLCs"+
+				"and therefore cannot be deleted: %w",
+				paymentHash.String(), err)
+		}
+
+		// Delete the failed HTLC attempts we found.
+		if failedHtlcsOnly {
+			toDelete, err := fetchFailedHtlcKeys(bucket)
+			if err != nil {
+				return err
+			}
+
+			htlcsBucket := bucket.NestedReadWriteBucket(
+				paymentHtlcsBucket,
+			)
+
+			for _, htlcID := range toDelete {
+				err = htlcsBucket.Delete(
+					htlcBucketKey(
+						htlcAttemptInfoKey, htlcID,
+					),
+				)
+				if err != nil {
+					return err
+				}
+
+				err = htlcsBucket.Delete(
+					htlcBucketKey(htlcFailInfoKey, htlcID),
+				)
+				if err != nil {
+					return err
+				}
+
+				err = htlcsBucket.Delete(
+					htlcBucketKey(
+						htlcSettleInfoKey, htlcID,
+					),
+				)
+				if err != nil {
+					return err
+				}
+			}
+
+			return nil
+		}
+
+		seqNrs, err := fetchSequenceNumbers(bucket)
+		if err != nil {
+			return err
+		}
+
+		err = payments.DeleteNestedBucket(paymentHash[:])
+		if err != nil {
+			return err
+		}
+
+		indexBucket := tx.ReadWriteBucket(paymentsIndexBucket)
+		for _, k := range seqNrs {
+			if err := indexBucket.Delete(k); err != nil {
+				return err
+			}
+		}
+
+		return nil
+	}, func() {})
+}
+
+// DeletePayments deletes all completed and failed payments from the DB. If
+// failedOnly is set, only failed payments will be considered for deletion. If
+// failedHtlcsOnly is set, the payment itself won't be deleted, only failed HTLC
+// attempts. The method returns the number of deleted payments, which is always
+// 0 if failedHtlcsOnly is set.
+func (p *KVStore) DeletePayments(_ context.Context, failedOnly,
+	failedHtlcsOnly bool) (int, error) {
+
+	var numPayments int
+	err := kvdb.Update(p.db, func(tx kvdb.RwTx) error {
+		payments := tx.ReadWriteBucket(paymentsRootBucket)
+		if payments == nil {
+			return nil
+		}
+
+		var (
+			// deleteBuckets is the set of payment buckets we need
+			// to delete.
+			deleteBuckets [][]byte
+
+			// deleteIndexes is the set of indexes pointing to these
+			// payments that need to be deleted.
+			deleteIndexes [][]byte
+
+			// deleteHtlcs maps a payment hash to the HTLC IDs we
+			// want to delete for that payment.
+			deleteHtlcs = make(map[lntypes.Hash][][]byte)
+		)
+		err := payments.ForEach(func(k, _ []byte) error {
+			bucket := payments.NestedReadBucket(k)
+			if bucket == nil {
+				// We only expect sub-buckets to be found in
+				// this top-level bucket.
+				return fmt.Errorf("non bucket element in " +
+					"payments bucket")
+			}
+
+			// If the status is InFlight, we cannot safely delete
+			// the payment information, so we return early.
+			paymentStatus, err := fetchPaymentStatus(bucket)
+			if err != nil {
+				return err
+			}
+
+			// If the payment has inflight HTLCs, we cannot safely
+			// delete the payment information, so we return an nil
+			// to skip it.
+			if err := paymentStatus.removable(); err != nil {
+				return nil
+			}
+
+			// If we requested to only delete failed payments, we
+			// can return if this one is not.
+			if failedOnly && paymentStatus != StatusFailed {
+				return nil
+			}
+
+			// If we are only deleting failed HTLCs, fetch them.
+			if failedHtlcsOnly {
+				toDelete, err := fetchFailedHtlcKeys(bucket)
+				if err != nil {
+					return err
+				}
+
+				hash, err := lntypes.MakeHash(k)
+				if err != nil {
+					return err
+				}
+
+				deleteHtlcs[hash] = toDelete
+
+				// We return, we are only deleting attempts.
+				return nil
+			}
+
+			// Add the bucket to the set of buckets we can delete.
+			deleteBuckets = append(deleteBuckets, k)
+
+			// Get all the sequence number associated with the
+			// payment, including duplicates.
+			seqNrs, err := fetchSequenceNumbers(bucket)
+			if err != nil {
+				return err
+			}
+
+			deleteIndexes = append(deleteIndexes, seqNrs...)
+			numPayments++
+
+			return nil
+		})
+		if err != nil {
+			return err
+		}
+
+		// Delete the failed HTLC attempts we found.
+		for hash, htlcIDs := range deleteHtlcs {
+			bucket := payments.NestedReadWriteBucket(hash[:])
+			htlcsBucket := bucket.NestedReadWriteBucket(
+				paymentHtlcsBucket,
+			)
+
+			for _, aid := range htlcIDs {
+				if err := htlcsBucket.Delete(
+					htlcBucketKey(htlcAttemptInfoKey, aid),
+				); err != nil {
+					return err
+				}
+
+				if err := htlcsBucket.Delete(
+					htlcBucketKey(htlcFailInfoKey, aid),
+				); err != nil {
+					return err
+				}
+
+				if err := htlcsBucket.Delete(
+					htlcBucketKey(htlcSettleInfoKey, aid),
+				); err != nil {
+					return err
+				}
+			}
+		}
+
+		for _, k := range deleteBuckets {
+			if err := payments.DeleteNestedBucket(k); err != nil {
+				return err
+			}
+		}
+
+		// Get our index bucket and delete all indexes pointing to the
+		// payments we are deleting.
+		indexBucket := tx.ReadWriteBucket(paymentsIndexBucket)
+		for _, k := range deleteIndexes {
+			if err := indexBucket.Delete(k); err != nil {
+				return err
+			}
+		}
+
+		return nil
+	}, func() {
+		numPayments = 0
+	})
+	if err != nil {
+		return 0, err
+	}
+
+	return numPayments, nil
+}
+
+// fetchSequenceNumbers fetches all the sequence numbers associated with a
+// payment, including those belonging to any duplicate payments.
+func fetchSequenceNumbers(paymentBucket kvdb.RBucket) ([][]byte, error) {
+	seqNum := paymentBucket.Get(paymentSequenceKey)
+	if seqNum == nil {
+		return nil, errors.New("expected sequence number")
+	}
+
+	sequenceNumbers := [][]byte{seqNum}
+
+	// Get the duplicate payments bucket, if it has no duplicates, just
+	// return early with the payment sequence number.
+	duplicates := paymentBucket.NestedReadBucket(duplicatePaymentsBucket)
+	if duplicates == nil {
+		return sequenceNumbers, nil
+	}
+
+	// If we do have duplicated, they are keyed by sequence number, so we
+	// iterate through the duplicates bucket and add them to our set of
+	// sequence numbers.
+	if err := duplicates.ForEach(func(k, v []byte) error {
+		sequenceNumbers = append(sequenceNumbers, k)
+		return nil
+	}); err != nil {
+		return nil, err
+	}
+
+	return sequenceNumbers, nil
+}
+
+func serializePaymentCreationInfo(w io.Writer, c *PaymentCreationInfo) error {
+	var scratch [8]byte
+
+	if _, err := w.Write(c.PaymentIdentifier[:]); err != nil {
+		return err
+	}
+
+	byteOrder.PutUint64(scratch[:], uint64(c.Value))
+	if _, err := w.Write(scratch[:]); err != nil {
+		return err
+	}
+
+	if err := serializeTime(w, c.CreationTime); err != nil {
+		return err
+	}
+
+	byteOrder.PutUint32(scratch[:4], uint32(len(c.PaymentRequest)))
+	if _, err := w.Write(scratch[:4]); err != nil {
+		return err
+	}
+
+	if _, err := w.Write(c.PaymentRequest); err != nil {
+		return err
+	}
+
+	// Any remaining bytes are TLV encoded records. Currently, these are
+	// only the custom records provided by the user to be sent to the first
+	// hop. But this can easily be extended with further records by merging
+	// the records into a single TLV stream.
+	err := c.FirstHopCustomRecords.SerializeTo(w)
+	if err != nil {
+		return err
+	}
+
+	return nil
+}
+
+func deserializePaymentCreationInfo(r io.Reader) (*PaymentCreationInfo,
+	error) {
+
+	var scratch [8]byte
+
+	c := &PaymentCreationInfo{}
+
+	if _, err := io.ReadFull(r, c.PaymentIdentifier[:]); err != nil {
+		return nil, err
+	}
+
+	if _, err := io.ReadFull(r, scratch[:]); err != nil {
+		return nil, err
+	}
+	c.Value = lnwire.MilliSatoshi(byteOrder.Uint64(scratch[:]))
+
+	creationTime, err := deserializeTime(r)
+	if err != nil {
+		return nil, err
+	}
+	c.CreationTime = creationTime
+
+	if _, err := io.ReadFull(r, scratch[:4]); err != nil {
+		return nil, err
+	}
+
+	reqLen := byteOrder.Uint32(scratch[:4])
+	payReq := make([]byte, reqLen)
+	if reqLen > 0 {
+		if _, err := io.ReadFull(r, payReq); err != nil {
+			return nil, err
+		}
+	}
+	c.PaymentRequest = payReq
+
+	// Any remaining bytes are TLV encoded records. Currently, these are
+	// only the custom records provided by the user to be sent to the first
+	// hop. But this can easily be extended with further records by merging
+	// the records into a single TLV stream.
+	c.FirstHopCustomRecords, err = lnwire.ParseCustomRecordsFrom(r)
+	if err != nil {
+		return nil, err
+	}
+
+	return c, nil
+}
+
+func serializeHTLCAttemptInfo(w io.Writer, a *HTLCAttemptInfo) error {
+	if err := WriteElements(w, a.sessionKey); err != nil {
+		return err
+	}
+
+	if err := SerializeRoute(w, a.Route); err != nil {
+		return err
+	}
+
+	if err := serializeTime(w, a.AttemptTime); err != nil {
+		return err
+	}
+
+	// If the hash is nil we can just return.
+	if a.Hash == nil {
+		return nil
+	}
+
+	if _, err := w.Write(a.Hash[:]); err != nil {
+		return err
+	}
+
+	// Merge the fixed/known records together with the custom records to
+	// serialize them as a single blob. We can't do this in SerializeRoute
+	// because we're in the middle of the byte stream there. We can only do
+	// TLV serialization at the end of the stream, since EOF is allowed for
+	// a stream if no more data is expected.
+	producers := []tlv.RecordProducer{
+		&a.Route.FirstHopAmount,
+	}
+	tlvData, err := lnwire.MergeAndEncode(
+		producers, nil, a.Route.FirstHopWireCustomRecords,
+	)
+	if err != nil {
+		return err
+	}
+
+	if _, err := w.Write(tlvData); err != nil {
+		return err
+	}
+
+	return nil
+}
+
+func deserializeHTLCAttemptInfo(r io.Reader) (*HTLCAttemptInfo, error) {
+	a := &HTLCAttemptInfo{}
+	err := ReadElements(r, &a.sessionKey)
+	if err != nil {
+		return nil, err
+	}
+
+	a.Route, err = DeserializeRoute(r)
+	if err != nil {
+		return nil, err
+	}
+
+	a.AttemptTime, err = deserializeTime(r)
+	if err != nil {
+		return nil, err
+	}
+
+	hash := lntypes.Hash{}
+	_, err = io.ReadFull(r, hash[:])
+
+	switch {
+	// Older payment attempts wouldn't have the hash set, in which case we
+	// can just return.
+	case errors.Is(err, io.EOF), errors.Is(err, io.ErrUnexpectedEOF):
+		return a, nil
+
+	case err != nil:
+		return nil, err
+
+	default:
+	}
+
+	a.Hash = &hash
+
+	// Read any remaining data (if any) and parse it into the known records
+	// and custom records.
+	extraData, err := io.ReadAll(r)
+	if err != nil {
+		return nil, err
+	}
+
+	customRecords, _, _, err := lnwire.ParseAndExtractCustomRecords(
+		extraData, &a.Route.FirstHopAmount,
+	)
+	if err != nil {
+		return nil, err
+	}
+
+	a.Route.FirstHopWireCustomRecords = customRecords
+
+	return a, nil
+}
+
+func serializeHop(w io.Writer, h *Hop) error {
+	if err := WriteElements(w,
+		h.PubKeyBytes[:],
+		h.ChannelID,
+		h.OutgoingTimeLock,
+		h.AmtToForward,
+	); err != nil {
+		return err
+	}
+
+	if err := binary.Write(w, byteOrder, h.LegacyPayload); err != nil {
+		return err
+	}
+
+	// For legacy payloads, we don't need to write any TLV records, so
+	// we'll write a zero indicating the our serialized TLV map has no
+	// records.
+	if h.LegacyPayload {
+		return WriteElements(w, uint32(0))
+	}
+
+	// Gather all non-primitive TLV records so that they can be serialized
+	// as a single blob.
+	//
+	// TODO(conner): add migration to unify all fields in a single TLV
+	// blobs. The split approach will cause headaches down the road as more
+	// fields are added, which we can avoid by having a single TLV stream
+	// for all payload fields.
+	var records []tlv.Record
+	if h.MPP != nil {
+		records = append(records, h.MPP.Record())
+	}
+
+	// Add blinding point and encrypted data if present.
+	if h.EncryptedData != nil {
+		records = append(records, record.NewEncryptedDataRecord(
+			&h.EncryptedData,
+		))
+	}
+
+	if h.BlindingPoint != nil {
+		records = append(records, record.NewBlindingPointRecord(
+			&h.BlindingPoint,
+		))
+	}
+
+	if h.AMP != nil {
+		records = append(records, h.AMP.Record())
+	}
+
+	if h.Metadata != nil {
+		records = append(records, record.NewMetadataRecord(&h.Metadata))
+	}
+
+	if h.TotalAmtMsat != 0 {
+		totalMsatInt := uint64(h.TotalAmtMsat)
+		records = append(
+			records, record.NewTotalAmtMsatBlinded(&totalMsatInt),
+		)
+	}
+
+	// Final sanity check to absolutely rule out custom records that are not
+	// custom and write into the standard range.
+	if err := h.CustomRecords.Validate(); err != nil {
+		return err
+	}
+
+	// Convert custom records to tlv and add to the record list.
+	// MapToRecords sorts the list, so adding it here will keep the list
+	// canonical.
+	tlvRecords := tlv.MapToRecords(h.CustomRecords)
+	records = append(records, tlvRecords...)
+
+	// Otherwise, we'll transform our slice of records into a map of the
+	// raw bytes, then serialize them in-line with a length (number of
+	// elements) prefix.
+	mapRecords, err := tlv.RecordsToMap(records)
+	if err != nil {
+		return err
+	}
+
+	numRecords := uint32(len(mapRecords))
+	if err := WriteElements(w, numRecords); err != nil {
+		return err
+	}
+
+	for recordType, rawBytes := range mapRecords {
+		if err := WriteElements(w, recordType); err != nil {
+			return err
+		}
+
+		if err := wire.WriteVarBytes(w, 0, rawBytes); err != nil {
+			return err
+		}
+	}
+
+	return nil
+}
+
+// maxOnionPayloadSize is the largest Sphinx payload possible, so we don't need
+// to read/write a TLV stream larger than this.
+const maxOnionPayloadSize = 1300
+
+func deserializeHop(r io.Reader) (*Hop, error) {
+	h := &Hop{}
+
+	var pub []byte
+	if err := ReadElements(r, &pub); err != nil {
+		return nil, err
+	}
+	copy(h.PubKeyBytes[:], pub)
+
+	if err := ReadElements(r,
+		&h.ChannelID, &h.OutgoingTimeLock, &h.AmtToForward,
+	); err != nil {
+		return nil, err
+	}
+
+	// TODO(roasbeef): change field to allow LegacyPayload false to be the
+	// legacy default?
+	err := binary.Read(r, byteOrder, &h.LegacyPayload)
+	if err != nil {
+		return nil, err
+	}
+
+	var numElements uint32
+	if err := ReadElements(r, &numElements); err != nil {
+		return nil, err
+	}
+
+	// If there're no elements, then we can return early.
+	if numElements == 0 {
+		return h, nil
+	}
+
+	tlvMap := make(map[uint64][]byte)
+	for i := uint32(0); i < numElements; i++ {
+		var tlvType uint64
+		if err := ReadElements(r, &tlvType); err != nil {
+			return nil, err
+		}
+
+		rawRecordBytes, err := wire.ReadVarBytes(
+			r, 0, maxOnionPayloadSize, "tlv",
+		)
+		if err != nil {
+			return nil, err
+		}
+
+		tlvMap[tlvType] = rawRecordBytes
+	}
+
+	// If the MPP type is present, remove it from the generic TLV map and
+	// parse it back into a proper MPP struct.
+	//
+	// TODO(conner): add migration to unify all fields in a single TLV
+	// blobs. The split approach will cause headaches down the road as more
+	// fields are added, which we can avoid by having a single TLV stream
+	// for all payload fields.
+	mppType := uint64(record.MPPOnionType)
+	if mppBytes, ok := tlvMap[mppType]; ok {
+		delete(tlvMap, mppType)
+
+		var (
+			mpp    = &record.MPP{}
+			mppRec = mpp.Record()
+			r      = bytes.NewReader(mppBytes)
+		)
+		err := mppRec.Decode(r, uint64(len(mppBytes)))
+		if err != nil {
+			return nil, err
+		}
+		h.MPP = mpp
+	}
+
+	// If encrypted data or blinding key are present, remove them from
+	// the TLV map and parse into proper types.
+	encryptedDataType := uint64(record.EncryptedDataOnionType)
+	if data, ok := tlvMap[encryptedDataType]; ok {
+		delete(tlvMap, encryptedDataType)
+		h.EncryptedData = data
+	}
+
+	blindingType := uint64(record.BlindingPointOnionType)
+	if blindingPoint, ok := tlvMap[blindingType]; ok {
+		delete(tlvMap, blindingType)
+
+		h.BlindingPoint, err = btcec.ParsePubKey(blindingPoint)
+		if err != nil {
+			return nil, fmt.Errorf("invalid blinding point: %w",
+				err)
+		}
+	}
+
+	ampType := uint64(record.AMPOnionType)
+	if ampBytes, ok := tlvMap[ampType]; ok {
+		delete(tlvMap, ampType)
+
+		var (
+			amp    = &record.AMP{}
+			ampRec = amp.Record()
+			r      = bytes.NewReader(ampBytes)
+		)
+		err := ampRec.Decode(r, uint64(len(ampBytes)))
+		if err != nil {
+			return nil, err
+		}
+		h.AMP = amp
+	}
+
+	// If the metadata type is present, remove it from the tlv map and
+	// populate directly on the hop.
+	metadataType := uint64(record.MetadataOnionType)
+	if metadata, ok := tlvMap[metadataType]; ok {
+		delete(tlvMap, metadataType)
+
+		h.Metadata = metadata
+	}
+
+	totalAmtMsatType := uint64(record.TotalAmtMsatBlindedType)
+	if totalAmtMsat, ok := tlvMap[totalAmtMsatType]; ok {
+		delete(tlvMap, totalAmtMsatType)
+
+		var (
+			totalAmtMsatInt uint64
+			buf             [8]byte
+		)
+		if err := tlv.DTUint64(
+			bytes.NewReader(totalAmtMsat),
+			&totalAmtMsatInt,
+			&buf,
+			uint64(len(totalAmtMsat)),
+		); err != nil {
+			return nil, err
+		}
+
+		h.TotalAmtMsat = lnwire.MilliSatoshi(totalAmtMsatInt)
+	}
+
+	h.CustomRecords = tlvMap
+
+	return h, nil
+}
+
+// SerializeRoute serializes a route.
+func SerializeRoute(w io.Writer, r Route) error {
+	if err := WriteElements(w,
+		r.TotalTimeLock, r.TotalAmount, r.SourcePubKey[:],
+	); err != nil {
+		return err
+	}
+
+	if err := WriteElements(w, uint32(len(r.Hops))); err != nil {
+		return err
+	}
+
+	for _, h := range r.Hops {
+		if err := serializeHop(w, h); err != nil {
+			return err
+		}
+	}
+
+	// Any new/extra TLV data is encoded in serializeHTLCAttemptInfo!
+
+	return nil
+}
+
+// DeserializeRoute deserializes a route.
+func DeserializeRoute(r io.Reader) (Route, error) {
+	rt := Route{}
+	if err := ReadElements(r,
+		&rt.TotalTimeLock, &rt.TotalAmount,
+	); err != nil {
+		return rt, err
+	}
+
+	var pub []byte
+	if err := ReadElements(r, &pub); err != nil {
+		return rt, err
+	}
+	copy(rt.SourcePubKey[:], pub)
+
+	var numHops uint32
+	if err := ReadElements(r, &numHops); err != nil {
+		return rt, err
+	}
+
+	var hops []*Hop
+	for i := uint32(0); i < numHops; i++ {
+		hop, err := deserializeHop(r)
+		if err != nil {
+			return rt, err
+		}
+		hops = append(hops, hop)
+	}
+	rt.Hops = hops
+
+	// Any new/extra TLV data is decoded in deserializeHTLCAttemptInfo!
+
+	return rt, nil
+}
+
+// serializeHTLCSettleInfo serializes the details of a settled htlc.
+func serializeHTLCSettleInfo(w io.Writer, s *HTLCSettleInfo) error {
+	if _, err := w.Write(s.Preimage[:]); err != nil {
+		return err
+	}
+
+	if err := serializeTime(w, s.SettleTime); err != nil {
+		return err
+	}
+
+	return nil
+}
+
+// deserializeHTLCSettleInfo deserializes the details of a settled htlc.
+func deserializeHTLCSettleInfo(r io.Reader) (*HTLCSettleInfo, error) {
+	s := &HTLCSettleInfo{}
+	if _, err := io.ReadFull(r, s.Preimage[:]); err != nil {
+		return nil, err
+	}
+
+	var err error
+	s.SettleTime, err = deserializeTime(r)
+	if err != nil {
+		return nil, err
+	}
+
+	return s, nil
+}
+
+// serializeHTLCFailInfo serializes the details of a failed htlc including the
+// wire failure.
+func serializeHTLCFailInfo(w io.Writer, f *HTLCFailInfo) error {
+	if err := serializeTime(w, f.FailTime); err != nil {
+		return err
+	}
+
+	// Write failure. If there is no failure message, write an empty
+	// byte slice.
+	var messageBytes bytes.Buffer
+	if f.Message != nil {
+		err := lnwire.EncodeFailureMessage(&messageBytes, f.Message, 0)
+		if err != nil {
+			return err
+		}
+	}
+	if err := wire.WriteVarBytes(w, 0, messageBytes.Bytes()); err != nil {
+		return err
+	}
+
+	return WriteElements(w, byte(f.Reason), f.FailureSourceIndex)
+}
+
+// deserializeHTLCFailInfo deserializes the details of a failed htlc including
+// the wire failure.
+func deserializeHTLCFailInfo(r io.Reader) (*HTLCFailInfo, error) {
+	f := &HTLCFailInfo{}
+	var err error
+	f.FailTime, err = deserializeTime(r)
+	if err != nil {
+		return nil, err
+	}
+
+	// Read failure.
+	failureBytes, err := wire.ReadVarBytes(
+		r, 0, math.MaxUint16, "failure",
+	)
+	if err != nil {
+		return nil, err
+	}
+	if len(failureBytes) > 0 {
+		f.Message, err = lnwire.DecodeFailureMessage(
+			bytes.NewReader(failureBytes), 0,
+		)
+		if err != nil &&
+			!errors.Is(err, lnwire.ErrParsingExtraTLVBytes) {
+
+			return nil, err
+		}
+
+		// In case we have an invalid TLV stream regarding the extra
+		// tlv data we still continue with the decoding of the
+		// HTLCFailInfo.
+		if errors.Is(err, lnwire.ErrParsingExtraTLVBytes) {
+			log.Warnf("Failed to decode extra TLV bytes for "+
+				"failure message: %v", err)
+		}
+	}
+
+	var reason byte
+	err = ReadElements(r, &reason, &f.FailureSourceIndex)
+	if err != nil {
+		return nil, err
+	}
+	f.Reason = HTLCFailReason(reason)
+
+	return f, nil
+}
diff --git a/payments/db/migration1/log.go b/payments/db/migration1/log.go
new file mode 100644
index 0000000..52f1f75
--- /dev/null
+++ b/payments/db/migration1/log.go
@@ -0,0 +1,32 @@
+package migration1
+
+import (
+	"github.com/btcsuite/btclog/v2"
+	"github.com/lightningnetwork/lnd/build"
+)
+
+// log is a logger that is initialized with no output filters.  This
+// means the package will not perform any logging by default until the caller
+// requests it.
+var log btclog.Logger
+
+// Subsystem defines the logging identifier for this subsystem.
+const Subsystem = "PYDB"
+
+// The default amount of logging is none.
+func init() {
+	UseLogger(build.NewSubLogger(Subsystem, nil))
+}
+
+// DisableLog disables all library log output.  Logging output is disabled
+// by default until UseLogger is called.
+func DisableLog() {
+	UseLogger(btclog.Disabled)
+}
+
+// UseLogger uses a specified Logger to output package logging info.
+// This should be used in preference to SetLogWriter if the caller is also
+// using btclog.
+func UseLogger(logger btclog.Logger) {
+	log = logger
+}
diff --git a/payments/db/migration1/options.go b/payments/db/migration1/options.go
new file mode 100644
index 0000000..382afb2
--- /dev/null
+++ b/payments/db/migration1/options.go
@@ -0,0 +1,26 @@
+package migration1
+
+// StoreOptions holds parameters for the KVStore.
+type StoreOptions struct {
+	// NoMigration allows to open the database in readonly mode
+	NoMigration bool
+}
+
+// DefaultOptions returns a StoreOptions populated with default values.
+func DefaultOptions() *StoreOptions {
+	return &StoreOptions{
+		NoMigration: false,
+	}
+}
+
+// OptionModifier is a function signature for modifying the default
+// StoreOptions.
+type OptionModifier func(*StoreOptions)
+
+// WithNoMigration allows the database to be opened in read only mode by
+// disabling migrations.
+func WithNoMigration(b bool) OptionModifier {
+	return func(o *StoreOptions) {
+		o.NoMigration = b
+	}
+}
diff --git a/payments/db/migration1/payment.go b/payments/db/migration1/payment.go
new file mode 100644
index 0000000..53a3d81
--- /dev/null
+++ b/payments/db/migration1/payment.go
@@ -0,0 +1,689 @@
+package migration1
+
+import (
+	"errors"
+	"fmt"
+	"time"
+
+	"github.com/btcsuite/btcd/btcec/v2"
+	sphinx "github.com/lightningnetwork/lightning-onion"
+	"github.com/lightningnetwork/lnd/lntypes"
+	"github.com/lightningnetwork/lnd/lnwire"
+)
+
+// FailureReason encodes the reason a payment ultimately failed.
+type FailureReason byte
+
+const (
+	// FailureReasonTimeout indicates that the payment did timeout before a
+	// successful payment attempt was made.
+	FailureReasonTimeout FailureReason = 0
+
+	// FailureReasonNoRoute indicates no successful route to the
+	// destination was found during path finding.
+	FailureReasonNoRoute FailureReason = 1
+
+	// FailureReasonError indicates that an unexpected error happened during
+	// payment.
+	FailureReasonError FailureReason = 2
+
+	// FailureReasonPaymentDetails indicates that either the hash is unknown
+	// or the final cltv delta or amount is incorrect.
+	FailureReasonPaymentDetails FailureReason = 3
+
+	// FailureReasonInsufficientBalance indicates that we didn't have enough
+	// balance to complete the payment.
+	FailureReasonInsufficientBalance FailureReason = 4
+
+	// FailureReasonCanceled indicates that the payment was canceled by the
+	// user.
+	FailureReasonCanceled FailureReason = 5
+
+	// TODO(joostjager): Add failure reasons for:
+	// LocalLiquidityInsufficient, RemoteCapacityInsufficient.
+)
+
+// Error returns a human-readable error string for the FailureReason.
+func (r FailureReason) Error() string {
+	return r.String()
+}
+
+// String returns a human-readable FailureReason.
+func (r FailureReason) String() string {
+	switch r {
+	case FailureReasonTimeout:
+		return "timeout"
+	case FailureReasonNoRoute:
+		return "no_route"
+	case FailureReasonError:
+		return "error"
+	case FailureReasonPaymentDetails:
+		return "incorrect_payment_details"
+	case FailureReasonInsufficientBalance:
+		return "insufficient_balance"
+	case FailureReasonCanceled:
+		return "canceled"
+	}
+
+	return "unknown"
+}
+
+// PaymentCreationInfo is the information necessary to have ready when
+// initiating a payment, moving it into state InFlight.
+type PaymentCreationInfo struct {
+	// PaymentIdentifier is the hash this payment is paying to in case of
+	// non-AMP payments, and the SetID for AMP payments.
+	PaymentIdentifier lntypes.Hash
+
+	// Value is the amount we are paying.
+	Value lnwire.MilliSatoshi
+
+	// CreationTime is the time when this payment was initiated.
+	CreationTime time.Time
+
+	// PaymentRequest is the full payment request, if any.
+	PaymentRequest []byte
+
+	// FirstHopCustomRecords are the TLV records that are to be sent to the
+	// first hop of this payment. These records will be transmitted via the
+	// wire message (UpdateAddHTLC) only and therefore do not affect the
+	// onion payload size.
+	FirstHopCustomRecords lnwire.CustomRecords
+}
+
+// String returns a human-readable description of the payment creation info.
+func (p *PaymentCreationInfo) String() string {
+	return fmt.Sprintf("payment_id=%v, amount=%v, created_at=%v",
+		p.PaymentIdentifier, p.Value, p.CreationTime)
+}
+
+// HTLCAttemptInfo contains static information about a specific HTLC attempt
+// for a payment. This information is used by the router to handle any errors
+// coming back after an attempt is made, and to query the switch about the
+// status of the attempt.
+type HTLCAttemptInfo struct {
+	// AttemptID is the unique ID used for this attempt.
+	AttemptID uint64
+
+	// sessionKey is the raw bytes ephemeral key used for this attempt.
+	// These bytes are lazily read off disk to save ourselves the expensive
+	// EC operations used by btcec.PrivKeyFromBytes.
+	sessionKey [btcec.PrivKeyBytesLen]byte
+
+	// cachedSessionKey is our fully deserialized sesionKey. This value
+	// may be nil if the attempt has just been read from disk and its
+	// session key has not been used yet.
+	cachedSessionKey *btcec.PrivateKey
+
+	// Route is the route attempted to send the HTLC.
+	Route Route
+
+	// AttemptTime is the time at which this HTLC was attempted.
+	AttemptTime time.Time
+
+	// Hash is the hash used for this single HTLC attempt. For AMP payments
+	// this will differ across attempts, for non-AMP payments each attempt
+	// will use the same hash. This can be nil for older payment attempts,
+	// in which the payment's PaymentHash in the PaymentCreationInfo should
+	// be used.
+	Hash *lntypes.Hash
+
+	// onionBlob is the cached value for onion blob created from the sphinx
+	// construction.
+	onionBlob [lnwire.OnionPacketSize]byte
+
+	// circuit is the cached value for sphinx circuit.
+	circuit *sphinx.Circuit
+}
+
+// SessionKey returns the ephemeral key used for a htlc attempt. This function
+// performs expensive ec-ops to obtain the session key if it is not cached.
+func (h *HTLCAttemptInfo) SessionKey() *btcec.PrivateKey {
+	if h.cachedSessionKey == nil {
+		h.cachedSessionKey, _ = btcec.PrivKeyFromBytes(
+			h.sessionKey[:],
+		)
+	}
+
+	return h.cachedSessionKey
+}
+
+// HTLCAttempt contains information about a specific HTLC attempt for a given
+// payment. It contains the HTLCAttemptInfo used to send the HTLC, as well
+// as a timestamp and any known outcome of the attempt.
+type HTLCAttempt struct {
+	HTLCAttemptInfo
+
+	// Settle is the preimage of a successful payment. This serves as a
+	// proof of payment. It will only be non-nil for settled payments.
+	//
+	// NOTE: Can be nil if payment is not settled.
+	Settle *HTLCSettleInfo
+
+	// Fail is a failure reason code indicating the reason the payment
+	// failed. It is only non-nil for failed payments.
+	//
+	// NOTE: Can be nil if payment is not failed.
+	Failure *HTLCFailInfo
+}
+
+// HTLCSettleInfo encapsulates the information that augments an HTLCAttempt in
+// the event that the HTLC is successful.
+type HTLCSettleInfo struct {
+	// Preimage is the preimage of a successful HTLC. This serves as a proof
+	// of payment.
+	Preimage lntypes.Preimage
+
+	// SettleTime is the time at which this HTLC was settled.
+	SettleTime time.Time
+}
+
+// HTLCFailReason is the reason an htlc failed.
+type HTLCFailReason byte
+
+const (
+	// HTLCFailUnknown is recorded for htlcs that failed with an unknown
+	// reason.
+	HTLCFailUnknown HTLCFailReason = 0
+
+	// HTLCFailUnreadable is recorded for htlcs that had a failure message
+	// that couldn't be decrypted.
+	HTLCFailUnreadable HTLCFailReason = 1
+
+	// HTLCFailInternal is recorded for htlcs that failed because of an
+	// internal error.
+	HTLCFailInternal HTLCFailReason = 2
+
+	// HTLCFailMessage is recorded for htlcs that failed with a network
+	// failure message.
+	HTLCFailMessage HTLCFailReason = 3
+)
+
+// HTLCFailInfo encapsulates the information that augments an HTLCAttempt in the
+// event that the HTLC fails.
+type HTLCFailInfo struct {
+	// FailTime is the time at which this HTLC was failed.
+	FailTime time.Time
+
+	// Message is the wire message that failed this HTLC. This field will be
+	// populated when the failure reason is HTLCFailMessage.
+	Message lnwire.FailureMessage
+
+	// Reason is the failure reason for this HTLC.
+	Reason HTLCFailReason
+
+	// The position in the path of the intermediate or final node that
+	// generated the failure message. Position zero is the sender node. This
+	// field will be populated when the failure reason is either
+	// HTLCFailMessage or HTLCFailUnknown.
+	FailureSourceIndex uint32
+}
+
+// MPPaymentState wraps a series of info needed for a given payment, which is
+// used by both MPP and AMP. This is a memory representation of the payment's
+// current state and is updated whenever the payment is read from disk.
+type MPPaymentState struct {
+	// NumAttemptsInFlight specifies the number of HTLCs the payment is
+	// waiting results for.
+	NumAttemptsInFlight int
+
+	// RemainingAmt specifies how much more money to be sent.
+	RemainingAmt lnwire.MilliSatoshi
+
+	// FeesPaid specifies the total fees paid so far that can be used to
+	// calculate remaining fee budget.
+	FeesPaid lnwire.MilliSatoshi
+
+	// HasSettledHTLC is true if at least one of the payment's HTLCs is
+	// settled.
+	HasSettledHTLC bool
+
+	// PaymentFailed is true if the payment has been marked as failed with
+	// a reason.
+	PaymentFailed bool
+}
+
+// MPPayment is a wrapper around a payment's PaymentCreationInfo and
+// HTLCAttempts. All payments will have the PaymentCreationInfo set, any
+// HTLCs made in attempts to be completed will populated in the HTLCs slice.
+// Each populated HTLCAttempt represents an attempted HTLC, each of which may
+// have the associated Settle or Fail struct populated if the HTLC is no longer
+// in-flight.
+type MPPayment struct {
+	// SequenceNum is a unique identifier used to sort the payments in
+	// order of creation.
+	SequenceNum uint64
+
+	// Info holds all static information about this payment, and is
+	// populated when the payment is initiated.
+	Info *PaymentCreationInfo
+
+	// HTLCs holds the information about individual HTLCs that we send in
+	// order to make the payment.
+	HTLCs []HTLCAttempt
+
+	// FailureReason is the failure reason code indicating the reason the
+	// payment failed.
+	//
+	// NOTE: Will only be set once the daemon has given up on the payment
+	// altogether.
+	FailureReason *FailureReason
+
+	// Status is the current PaymentStatus of this payment.
+	Status PaymentStatus
+
+	// State is the current state of the payment that holds a number of key
+	// insights and is used to determine what to do on each payment loop
+	// iteration.
+	State *MPPaymentState
+}
+
+// Terminated returns a bool to specify whether the payment is in a terminal
+// state.
+func (m *MPPayment) Terminated() bool {
+	// If the payment is in terminal state, it cannot be updated.
+	return m.Status.updatable() != nil
+}
+
+// TerminalInfo returns any HTLC settle info recorded. If no settle info is
+// recorded, any payment level failure will be returned. If neither a settle
+// nor a failure is recorded, both return values will be nil.
+func (m *MPPayment) TerminalInfo() (*HTLCAttempt, *FailureReason) {
+	for _, h := range m.HTLCs {
+		if h.Settle != nil {
+			return &h, nil
+		}
+	}
+
+	return nil, m.FailureReason
+}
+
+// SentAmt returns the sum of sent amount and fees for HTLCs that are either
+// settled or still in flight.
+func (m *MPPayment) SentAmt() (lnwire.MilliSatoshi, lnwire.MilliSatoshi) {
+	var sent, fees lnwire.MilliSatoshi
+	for _, h := range m.HTLCs {
+		if h.Failure != nil {
+			continue
+		}
+
+		// The attempt was not failed, meaning the amount was
+		// potentially sent to the receiver.
+		sent += h.Route.ReceiverAmt()
+		fees += h.Route.TotalFees()
+	}
+
+	return sent, fees
+}
+
+// InFlightHTLCs returns the HTLCs that are still in-flight, meaning they have
+// not been settled or failed.
+func (m *MPPayment) InFlightHTLCs() []HTLCAttempt {
+	var inflights []HTLCAttempt
+	for _, h := range m.HTLCs {
+		if h.Settle != nil || h.Failure != nil {
+			continue
+		}
+
+		inflights = append(inflights, h)
+	}
+
+	return inflights
+}
+
+// GetAttempt returns the specified htlc attempt on the payment.
+func (m *MPPayment) GetAttempt(id uint64) (*HTLCAttempt, error) {
+	// TODO(yy): iteration can be slow, make it into a tree or use BS.
+	for _, htlc := range m.HTLCs {
+		htlc := htlc
+		if htlc.AttemptID == id {
+			return &htlc, nil
+		}
+	}
+
+	return nil, errors.New("htlc attempt not found on payment")
+}
+
+// Registrable returns an error to specify whether adding more HTLCs to the
+// payment with its current status is allowed. A payment can accept new HTLC
+// registrations when it's newly created, or none of its HTLCs is in a terminal
+// state.
+func (m *MPPayment) Registrable() error {
+	// If updating the payment is not allowed, we can't register new HTLCs.
+	// Otherwise, the status must be either `StatusInitiated` or
+	// `StatusInFlight`.
+	if err := m.Status.updatable(); err != nil {
+		return err
+	}
+
+	// Exit early if this is not inflight.
+	if m.Status != StatusInFlight {
+		return nil
+	}
+
+	// There are still inflight HTLCs and we need to check whether there
+	// are settled HTLCs or the payment is failed. If we already have
+	// settled HTLCs, we won't allow adding more HTLCs.
+	if m.State.HasSettledHTLC {
+		return ErrPaymentPendingSettled
+	}
+
+	// If the payment is already failed, we won't allow adding more HTLCs.
+	if m.State.PaymentFailed {
+		return ErrPaymentPendingFailed
+	}
+
+	// Otherwise we can add more HTLCs.
+	return nil
+}
+
+// setState creates and attaches a new MPPaymentState to the payment. It also
+// updates the payment's status based on its current state.
+func (m *MPPayment) setState() error {
+	// Fetch the total amount and fees that has already been sent in
+	// settled and still in-flight shards.
+	sentAmt, fees := m.SentAmt()
+
+	// Sanity check we haven't sent a value larger than the payment amount.
+	totalAmt := m.Info.Value
+	if sentAmt > totalAmt {
+		return fmt.Errorf("%w: sent=%v, total=%v",
+			ErrSentExceedsTotal, sentAmt, totalAmt)
+	}
+
+	// Get any terminal info for this payment.
+	settle, failure := m.TerminalInfo()
+
+	// Now determine the payment's status.
+	status, err := decidePaymentStatus(m.HTLCs, m.FailureReason)
+	if err != nil {
+		return err
+	}
+
+	// Update the payment state and status.
+	m.State = &MPPaymentState{
+		NumAttemptsInFlight: len(m.InFlightHTLCs()),
+		RemainingAmt:        totalAmt - sentAmt,
+		FeesPaid:            fees,
+		HasSettledHTLC:      settle != nil,
+		PaymentFailed:       failure != nil,
+	}
+	m.Status = status
+
+	return nil
+}
+
+// SetState calls the internal method setState. This is a temporary method
+// to be used by the tests in routing. Once the tests are updated to use mocks,
+// this method can be removed.
+//
+// TODO(yy): delete.
+func (m *MPPayment) SetState() error {
+	return m.setState()
+}
+
+// NeedWaitAttempts decides whether we need to hold creating more HTLC attempts
+// and wait for the results of the payment's inflight HTLCs. Return an error if
+// the payment is in an unexpected state.
+func (m *MPPayment) NeedWaitAttempts() (bool, error) {
+	// Check when the remainingAmt is not zero, which means we have more
+	// money to be sent.
+	if m.State.RemainingAmt != 0 {
+		switch m.Status {
+		// If the payment is newly created, no need to wait for HTLC
+		// results.
+		case StatusInitiated:
+			return false, nil
+
+		// If we have inflight HTLCs, we'll check if we have terminal
+		// states to decide if we need to wait.
+		case StatusInFlight:
+			// We still have money to send, and one of the HTLCs is
+			// settled. We'd stop sending money and wait for all
+			// inflight HTLC attempts to finish.
+			if m.State.HasSettledHTLC {
+				log.Warnf("payment=%v has remaining amount "+
+					"%v, yet at least one of its HTLCs is "+
+					"settled", m.Info.PaymentIdentifier,
+					m.State.RemainingAmt)
+
+				return true, nil
+			}
+
+			// The payment has a failure reason though we still
+			// have money to send, we'd stop sending money and wait
+			// for all inflight HTLC attempts to finish.
+			if m.State.PaymentFailed {
+				return true, nil
+			}
+
+			// Otherwise we don't need to wait for inflight HTLCs
+			// since we still have money to be sent.
+			return false, nil
+
+		// We need to send more money, yet the payment is already
+		// succeeded. Return an error in this case as the receiver is
+		// violating the protocol.
+		case StatusSucceeded:
+			return false, fmt.Errorf("%w: parts of the payment "+
+				"already succeeded but still have remaining "+
+				"amount %v", ErrPaymentInternal,
+				m.State.RemainingAmt)
+
+		// The payment is failed and we have no inflight HTLCs, no need
+		// to wait.
+		case StatusFailed:
+			return false, nil
+
+		// Unknown payment status.
+		default:
+			return false, fmt.Errorf("%w: %s",
+				ErrUnknownPaymentStatus, m.Status)
+		}
+	}
+
+	// Now we determine whether we need to wait when the remainingAmt is
+	// already zero.
+	switch m.Status {
+	// When the payment is newly created, yet the payment has no remaining
+	// amount, return an error.
+	case StatusInitiated:
+		return false, fmt.Errorf("%w: %v",
+			ErrPaymentInternal, m.Status)
+
+	// If the payment is inflight, we must wait.
+	//
+	// NOTE: an edge case is when all HTLCs are failed while the payment is
+	// not failed we'd still be in this inflight state. However, since the
+	// remainingAmt is zero here, it means we cannot be in that state as
+	// otherwise the remainingAmt would not be zero.
+	case StatusInFlight:
+		return true, nil
+
+	// If the payment is already succeeded, no need to wait.
+	case StatusSucceeded:
+		return false, nil
+
+	// If the payment is already failed, yet the remaining amount is zero,
+	// return an error as this indicates an error state. We will only each
+	// this status when there are no inflight HTLCs and the payment is
+	// marked as failed with a reason, which means the remainingAmt must
+	// not be zero because our sentAmt is zero.
+	case StatusFailed:
+		return false, fmt.Errorf("%w: %v",
+			ErrPaymentInternal, m.Status)
+
+	// Unknown payment status.
+	default:
+		return false, fmt.Errorf("%w: %s",
+			ErrUnknownPaymentStatus, m.Status)
+	}
+}
+
+// GetState returns the internal state of the payment.
+func (m *MPPayment) GetState() *MPPaymentState {
+	return m.State
+}
+
+// GetStatus returns the current status of the payment.
+func (m *MPPayment) GetStatus() PaymentStatus {
+	return m.Status
+}
+
+// GetHTLCs returns all the HTLCs for this payment.
+func (m *MPPayment) GetHTLCs() []HTLCAttempt {
+	return m.HTLCs
+}
+
+// AllowMoreAttempts is used to decide whether we can safely attempt more HTLCs
+// for a given payment state. Return an error if the payment is in an
+// unexpected state.
+func (m *MPPayment) AllowMoreAttempts() (bool, error) {
+	// Now check whether the remainingAmt is zero or not. If we don't have
+	// any remainingAmt, no more HTLCs should be made.
+	if m.State.RemainingAmt == 0 {
+		// If the payment is newly created, yet we don't have any
+		// remainingAmt, return an error.
+		if m.Status == StatusInitiated {
+			return false, fmt.Errorf("%w: initiated payment has "+
+				"zero remainingAmt",
+				ErrPaymentInternal)
+		}
+
+		// Otherwise, exit early since all other statuses with zero
+		// remainingAmt indicate no more HTLCs can be made.
+		return false, nil
+	}
+
+	// Otherwise, the remaining amount is not zero, we now decide whether
+	// to make more attempts based on the payment's current status.
+	//
+	// If at least one of the payment's attempts is settled, yet we haven't
+	// sent all the amount, it indicates something is wrong with the peer
+	// as the preimage is received. In this case, return an error state.
+	if m.Status == StatusSucceeded {
+		return false, fmt.Errorf("%w: payment already succeeded but "+
+			"still have remaining amount %v",
+			ErrPaymentInternal, m.State.RemainingAmt)
+	}
+
+	// Now check if we can register a new HTLC.
+	err := m.Registrable()
+	if err != nil {
+		log.Warnf("Payment(%v): cannot register HTLC attempt: %v, "+
+			"current status: %s", m.Info.PaymentIdentifier,
+			err, m.Status)
+
+		return false, nil
+	}
+
+	// Now we know we can register new HTLCs.
+	return true, nil
+}
+
+// verifyAttempt validates that a new HTLC attempt is compatible with the
+// existing payment and its in-flight HTLCs. This function checks:
+//  1. MPP (Multi-Path Payment) compatibility between attempts
+//  2. Blinded payment consistency
+//  3. Amount validation
+//  4. Total payment amount limits
+func verifyAttempt(payment *MPPayment, attempt *HTLCAttemptInfo) error {
+	// If the final hop has encrypted data, then we know this is a
+	// blinded payment. In blinded payments, MPP records are not set
+	// for split payments and the recipient is responsible for using
+	// a consistent PathID across the various encrypted data
+	// payloads that we received from them for this payment. All we
+	// need to check is that the total amount field for each HTLC
+	// in the split payment is correct.
+	isBlinded := len(attempt.Route.FinalHop().EncryptedData) != 0
+
+	// For blinded payments, the last hop must set the total amount.
+	if isBlinded {
+		if attempt.Route.FinalHop().TotalAmtMsat == 0 {
+			return ErrBlindedPaymentMissingTotalAmount
+		}
+	}
+
+	// Make sure any existing shards match the new one with regards
+	// to MPP options.
+	mpp := attempt.Route.FinalHop().MPP
+
+	// MPP records should not be set for attempts to blinded paths.
+	if isBlinded && mpp != nil {
+		return ErrMPPRecordInBlindedPayment
+	}
+
+	for _, h := range payment.InFlightHTLCs() {
+		hMpp := h.Route.FinalHop().MPP
+		hBlinded := len(h.Route.FinalHop().EncryptedData) != 0
+
+		// If this is a blinded payment, then no existing HTLCs
+		// should have MPP records.
+		if isBlinded && hMpp != nil {
+			return ErrMPPRecordInBlindedPayment
+		}
+
+		// If the payment is blinded (previous attempts used blinded
+		// paths) and the attempt is not, or vice versa, return an
+		// error.
+		if isBlinded != hBlinded {
+			return ErrMixedBlindedAndNonBlindedPayments
+		}
+
+		// If this is a blinded payment, then we just need to
+		// check that the TotalAmtMsat field for this shard
+		// is equal to that of any other shard in the same
+		// payment.
+		if isBlinded {
+			if attempt.Route.FinalHop().TotalAmtMsat !=
+				h.Route.FinalHop().TotalAmtMsat {
+
+				return ErrBlindedPaymentTotalAmountMismatch
+			}
+
+			continue
+		}
+
+		switch {
+		// We tried to register a non-MPP attempt for a MPP
+		// payment.
+		case mpp == nil && hMpp != nil:
+			return ErrMPPayment
+
+		// We tried to register a MPP shard for a non-MPP
+		// payment.
+		case mpp != nil && hMpp == nil:
+			return ErrNonMPPayment
+
+		// Non-MPP payment, nothing more to validate.
+		case mpp == nil:
+			continue
+		}
+
+		// Check that MPP options match.
+		if mpp.PaymentAddr() != hMpp.PaymentAddr() {
+			return ErrMPPPaymentAddrMismatch
+		}
+
+		if mpp.TotalMsat() != hMpp.TotalMsat() {
+			return ErrMPPTotalAmountMismatch
+		}
+	}
+
+	// If this is a non-MPP attempt, it must match the total amount
+	// exactly. Note that a blinded payment is considered an MPP
+	// attempt.
+	amt := attempt.Route.ReceiverAmt()
+	if !isBlinded && mpp == nil && amt != payment.Info.Value {
+		return ErrValueMismatch
+	}
+
+	// Ensure we aren't sending more than the total payment amount.
+	sentAmt, _ := payment.SentAmt()
+	if sentAmt+amt > payment.Info.Value {
+		return fmt.Errorf("%w: attempted=%v, payment amount=%v",
+			ErrValueExceedsAmt, sentAmt+amt, payment.Info.Value)
+	}
+
+	return nil
+}
diff --git a/payments/db/migration1/payment_status.go b/payments/db/migration1/payment_status.go
new file mode 100644
index 0000000..16c4b90
--- /dev/null
+++ b/payments/db/migration1/payment_status.go
@@ -0,0 +1,257 @@
+package migration1
+
+import (
+	"fmt"
+)
+
+// PaymentStatus represent current status of payment.
+type PaymentStatus byte
+
+const (
+	// NOTE: PaymentStatus = 0 was previously used for status unknown and
+	// is now deprecated.
+
+	// StatusInitiated is the status where a payment has just been
+	// initiated.
+	StatusInitiated PaymentStatus = 1
+
+	// StatusInFlight is the status where a payment has been initiated, but
+	// a response has not been received.
+	StatusInFlight PaymentStatus = 2
+
+	// StatusSucceeded is the status where a payment has been initiated and
+	// the payment was completed successfully.
+	StatusSucceeded PaymentStatus = 3
+
+	// StatusFailed is the status where a payment has been initiated and a
+	// failure result has come back.
+	StatusFailed PaymentStatus = 4
+)
+
+// errPaymentStatusUnknown is returned when a payment has an unknown status.
+var errPaymentStatusUnknown = fmt.Errorf("unknown payment status")
+
+// String returns readable representation of payment status.
+func (ps PaymentStatus) String() string {
+	switch ps {
+	case StatusInitiated:
+		return "Initiated"
+
+	case StatusInFlight:
+		return "In Flight"
+
+	case StatusSucceeded:
+		return "Succeeded"
+
+	case StatusFailed:
+		return "Failed"
+
+	default:
+		return "Unknown"
+	}
+}
+
+// initializable returns an error to specify whether initiating the payment
+// with its current status is allowed. A payment can only be initialized if it
+// hasn't been created yet or already failed.
+func (ps PaymentStatus) initializable() error {
+	switch ps {
+	// The payment has been created already. We will disallow creating it
+	// again in case other goroutines have already been creating HTLCs for
+	// it.
+	case StatusInitiated:
+		return ErrPaymentExists
+
+	// We already have an InFlight payment on the network. We will disallow
+	// any new payments.
+	case StatusInFlight:
+		return ErrPaymentInFlight
+
+	// The payment has been attempted and is succeeded so we won't allow
+	// creating it again.
+	case StatusSucceeded:
+		return ErrAlreadyPaid
+
+	// We allow retrying failed payments.
+	case StatusFailed:
+		return nil
+
+	default:
+		return fmt.Errorf("%w: %v", ErrUnknownPaymentStatus,
+			ps)
+	}
+}
+
+// removable returns an error to specify whether deleting the payment with its
+// current status is allowed. A payment cannot be safely deleted if it has
+// inflight HTLCs.
+func (ps PaymentStatus) removable() error {
+	switch ps {
+	// The payment has been created but has no HTLCs and can be removed.
+	case StatusInitiated:
+		return nil
+
+	// There are still inflight HTLCs and the payment needs to wait for the
+	// final outcomes.
+	case StatusInFlight:
+		return ErrPaymentInFlight
+
+	// The payment has been attempted and is succeeded and is allowed to be
+	// removed.
+	case StatusSucceeded:
+		return nil
+
+	// Failed payments are allowed to be removed.
+	case StatusFailed:
+		return nil
+
+	default:
+		return fmt.Errorf("%w: %v", ErrUnknownPaymentStatus,
+			ps)
+	}
+}
+
+// updatable returns an error to specify whether the payment's HTLCs can be
+// updated. A payment can update its HTLCs when it has inflight HTLCs.
+func (ps PaymentStatus) updatable() error {
+	switch ps {
+	// Newly created payments can be updated.
+	case StatusInitiated:
+		return nil
+
+	// Inflight payments can be updated.
+	case StatusInFlight:
+		return nil
+
+	// If the payment has a terminal condition, we won't allow any updates.
+	case StatusSucceeded:
+		return ErrPaymentAlreadySucceeded
+
+	case StatusFailed:
+		return ErrPaymentAlreadyFailed
+
+	default:
+		return fmt.Errorf("%w: %v", ErrUnknownPaymentStatus,
+			ps)
+	}
+}
+
+// decidePaymentStatus uses the payment's DB state to determine a memory status
+// that's used by the payment router to decide following actions.
+// Together, we use four variables to determine the payment's status,
+//   - inflight: whether there are any pending HTLCs.
+//   - settled: whether any of the HTLCs has been settled.
+//   - htlc failed: whether any of the HTLCs has been failed.
+//   - payment failed: whether the payment has been marked as failed.
+//
+// Based on the above variables, we derive the status using the following
+// table,
+// | inflight | settled | htlc failed | payment failed |         status       |
+// |:--------:|:-------:|:-----------:|:--------------:|:--------------------:|
+// |   true   |   true  |     true    |      true      |    StatusInFlight    |
+// |   true   |   true  |     true    |      false     |    StatusInFlight    |
+// |   true   |   true  |     false   |      true      |    StatusInFlight    |
+// |   true   |   true  |     false   |      false     |    StatusInFlight    |
+// |   true   |   false |     true    |      true      |    StatusInFlight    |
+// |   true   |   false |     true    |      false     |    StatusInFlight    |
+// |   true   |   false |     false   |      true      |    StatusInFlight    |
+// |   true   |   false |     false   |      false     |    StatusInFlight    |
+// |   false  |   true  |     true    |      true      |    StatusSucceeded   |
+// |   false  |   true  |     true    |      false     |    StatusSucceeded   |
+// |   false  |   true  |     false   |      true      |    StatusSucceeded   |
+// |   false  |   true  |     false   |      false     |    StatusSucceeded   |
+// |   false  |   false |     true    |      true      |      StatusFailed    |
+// |   false  |   false |     true    |      false     |    StatusInFlight    |
+// |   false  |   false |     false   |      true      |      StatusFailed    |
+// |   false  |   false |     false   |      false     |    StatusInitiated   |
+//
+// When `inflight`, `settled`, `htlc failed`, and `payment failed` are false,
+// this indicates the payment is newly created and hasn't made any HTLCs yet.
+// When `inflight` and `settled` are false, `htlc failed` is true yet `payment
+// failed` is false, this indicates all the payment's HTLCs have occurred a
+// temporarily failure and the payment is still in-flight.
+func decidePaymentStatus(htlcs []HTLCAttempt,
+	reason *FailureReason) (PaymentStatus, error) {
+
+	var (
+		inflight      bool
+		htlcSettled   bool
+		htlcFailed    bool
+		paymentFailed bool
+	)
+
+	// If we have a failure reason, the payment is failed.
+	if reason != nil {
+		paymentFailed = true
+	}
+
+	// Go through all HTLCs for this payment, check whether we have any
+	// settled HTLC, and any still in-flight.
+	for _, h := range htlcs {
+		if h.Failure != nil {
+			htlcFailed = true
+			continue
+		}
+
+		if h.Settle != nil {
+			htlcSettled = true
+			continue
+		}
+
+		// If any of the HTLCs are not failed nor settled, we
+		// still have inflight HTLCs.
+		inflight = true
+	}
+
+	// Use the DB state to determine the status of the payment.
+	switch {
+	// If we have inflight HTLCs, no matter we have settled or failed
+	// HTLCs, or the payment failed, we still consider it inflight so we
+	// inform upper systems to wait for the results.
+	case inflight:
+		return StatusInFlight, nil
+
+	// If we have no in-flight HTLCs, and at least one of the HTLCs is
+	// settled, the payment succeeded.
+	//
+	// NOTE: when reaching this case, paymentFailed could be true, which
+	// means we have a conflicting state for this payment. We choose to
+	// mark the payment as succeeded because it's the receiver's
+	// responsibility to only settle the payment iff all HTLCs are
+	// received.
+	case htlcSettled:
+		return StatusSucceeded, nil
+
+	// If we have no in-flight HTLCs, and the payment failure is set, the
+	// payment is considered failed.
+	//
+	// NOTE: when reaching this case, settled must be false.
+	case paymentFailed:
+		return StatusFailed, nil
+
+	// If we have no in-flight HTLCs, yet the payment is NOT failed, it
+	// means all the HTLCs are failed. In this case we can attempt more
+	// HTLCs.
+	//
+	// NOTE: when reaching this case, both settled and paymentFailed must
+	// be false.
+	case htlcFailed:
+		return StatusInFlight, nil
+
+	// If none of the HTLCs is either settled or failed, and we have no
+	// inflight HTLCs, this means the payment has no HTLCs created yet.
+	//
+	// NOTE: when reaching this case, both settled and paymentFailed must
+	// be false.
+	case !htlcFailed:
+		return StatusInitiated, nil
+
+	// Otherwise an impossible state is reached.
+	//
+	// NOTE: we should never end up here.
+	default:
+		log.Error("Impossible payment state reached")
+		return 0, fmt.Errorf("%w: payment is corrupted",
+			errPaymentStatusUnknown)
+	}
+}
diff --git a/payments/db/migration1/query.go b/payments/db/migration1/query.go
new file mode 100644
index 0000000..1fab2fb
--- /dev/null
+++ b/payments/db/migration1/query.go
@@ -0,0 +1,75 @@
+package migration1
+
+const (
+	// DefaultMaxPayments is the default maximum number of payments returned
+	// in the payments query pagination.
+	DefaultMaxPayments = 100
+)
+
+// Query represents a query to the payments database starting or ending
+// at a certain offset index. The number of retrieved records can be limited.
+type Query struct {
+	// IndexOffset determines the starting point of the payments query and
+	// is always exclusive. In normal order, the query starts at the next
+	// higher (available) index compared to IndexOffset. In reversed order,
+	// the query ends at the next lower (available) index compared to the
+	// IndexOffset. In the case of a zero index_offset, the query will start
+	// with the oldest payment when paginating forwards, or will end with
+	// the most recent payment when paginating backwards.
+	IndexOffset uint64
+
+	// MaxPayments is the maximal number of payments returned in the
+	// payments query.
+	MaxPayments uint64
+
+	// Reversed gives a meaning to the IndexOffset. If reversed is set to
+	// true, the query will fetch payments with indices lower than the
+	// IndexOffset, otherwise, it will return payments with indices greater
+	// than the IndexOffset.
+	Reversed bool
+
+	// If IncludeIncomplete is true, then return payments that have not yet
+	// fully completed. This means that pending payments, as well as failed
+	// payments will show up if this field is set to true.
+	IncludeIncomplete bool
+
+	// CountTotal indicates that all payments currently present in the
+	// payment index (complete and incomplete) should be counted.
+	CountTotal bool
+
+	// CreationDateStart, expressed in Unix seconds, if set, filters out
+	// all payments with a creation date greater than or equal to it.
+	CreationDateStart int64
+
+	// CreationDateEnd, expressed in Unix seconds, if set, filters out all
+	// payments with a creation date less than or equal to it.
+	CreationDateEnd int64
+}
+
+// Response contains the result of a query to the payments database.
+// It includes the set of payments that match the query and integers which
+// represent the index of the first and last item returned in the series of
+// payments. These integers allow callers to resume their query in the event
+// that the query's response exceeds the max number of returnable events.
+type Response struct {
+	// Payments is the set of payments returned from the database for the
+	// Query.
+	Payments []*MPPayment
+
+	// FirstIndexOffset is the index of the first element in the set of
+	// returned MPPayments. Callers can use this to resume their query
+	// in the event that the slice has too many events to fit into a single
+	// response. The offset can be used to continue reverse pagination.
+	FirstIndexOffset uint64
+
+	// LastIndexOffset is the index of the last element in the set of
+	// returned MPPayments. Callers can use this to resume their query
+	// in the event that the slice has too many events to fit into a single
+	// response. The offset can be used to continue forward pagination.
+	LastIndexOffset uint64
+
+	// TotalCount represents the total number of payments that are currently
+	// stored in the payment database. This will only be set if the
+	// CountTotal field in the query was set to true.
+	TotalCount uint64
+}
diff --git a/payments/db/migration1/route.go b/payments/db/migration1/route.go
new file mode 100644
index 0000000..dc9a221
--- /dev/null
+++ b/payments/db/migration1/route.go
@@ -0,0 +1,138 @@
+package migration1
+
+import (
+	"fmt"
+
+	"github.com/btcsuite/btcd/btcec/v2"
+	"github.com/lightningnetwork/lnd/lnwire"
+	"github.com/lightningnetwork/lnd/record"
+	"github.com/lightningnetwork/lnd/tlv"
+)
+
+// Vertex is a frozen local type representing a 33-byte compressed public key,
+// equivalent to route.Vertex at the time this migration was written.
+type Vertex [33]byte
+
+// NewVertex returns a new Vertex from a compressed public key.
+func NewVertex(pub *btcec.PublicKey) Vertex {
+	var v Vertex
+	copy(v[:], pub.SerializeCompressed())
+
+	return v
+}
+
+// NewVertexFromBytes returns a new Vertex from a serialized compressed public
+// key byte slice.
+func NewVertexFromBytes(b []byte) (Vertex, error) {
+	if len(b) != 33 {
+		return Vertex{}, fmt.Errorf("invalid vertex length %d, "+
+			"expected 33", len(b))
+	}
+
+	var v Vertex
+	copy(v[:], b)
+
+	return v, nil
+}
+
+// Hop is a frozen local snapshot of route.Hop, containing exactly the fields
+// that existed at the time this migration was written. This ensures the
+// migration's serialization boundary is independent of future changes to
+// route.Hop, in particular the planned removal of LegacyPayload once the KV
+// backend is phased out.
+type Hop struct {
+	// PubKeyBytes is the raw bytes of the public key of the target node.
+	PubKeyBytes Vertex
+
+	// ChannelID is the unique channel ID for the channel.
+	ChannelID uint64
+
+	// OutgoingTimeLock is the timelock value that should be used when
+	// crafting the _outgoing_ HTLC from this hop.
+	OutgoingTimeLock uint32
+
+	// AmtToForward is the amount that this hop will forward to the next
+	// hop.
+	AmtToForward lnwire.MilliSatoshi
+
+	// MPP encapsulates the data required for option_mpp. This field should
+	// only be set for the final hop.
+	MPP *record.MPP
+
+	// AMP encapsulates the data required for option_amp. This field should
+	// only be set for the final hop.
+	AMP *record.AMP
+
+	// CustomRecords if non-nil are a set of additional TLV records that
+	// should be included in the forwarding instructions for this node.
+	CustomRecords record.CustomSet
+
+	// LegacyPayload signals that this node doesn't understand the new TLV
+	// payload, so the legacy payload format must be used.
+	//
+	// NOTE: This field is preserved here even though it is marked for
+	// removal in the live route.Hop, because old KV data may have been
+	// serialized with LegacyPayload=true and the migration must be able to
+	// deserialize it correctly.
+	LegacyPayload bool
+
+	// Metadata is additional data sent along with the payment to the
+	// payee.
+	Metadata []byte
+
+	// EncryptedData is an encrypted data blob included for hops that are
+	// part of a blinded route.
+	EncryptedData []byte
+
+	// BlindingPoint is an ephemeral public key used by introduction nodes
+	// in blinded routes.
+	BlindingPoint *btcec.PublicKey
+
+	// TotalAmtMsat is the total amount for a blinded payment. This field
+	// should only be set for the final hop in a blinded path.
+	TotalAmtMsat lnwire.MilliSatoshi
+}
+
+// Route is a frozen local snapshot of route.Route, containing exactly the
+// fields that existed at the time this migration was written.
+type Route struct {
+	// TotalTimeLock is the cumulative (final) time lock across the entire
+	// route.
+	TotalTimeLock uint32
+
+	// TotalAmount is the total amount of funds required to complete a
+	// payment over this route, including fees.
+	TotalAmount lnwire.MilliSatoshi
+
+	// SourcePubKey is the pubkey of the node where this route originates.
+	SourcePubKey Vertex
+
+	// Hops contains details concerning the specific forwarding details at
+	// each hop.
+	Hops []*Hop
+
+	// FirstHopAmount is the amount that should actually be sent to the
+	// first hop. Only differs from TotalAmount for custom channels.
+	FirstHopAmount tlv.RecordT[
+		tlv.TlvType0, tlv.BigSizeT[lnwire.MilliSatoshi],
+	]
+
+	// FirstHopWireCustomRecords is a set of custom TLV records to include
+	// in the wire message sent to the first hop.
+	FirstHopWireCustomRecords lnwire.CustomRecords
+}
+
+// FinalHop returns the last hop in the route.
+func (r *Route) FinalHop() *Hop {
+	return r.Hops[len(r.Hops)-1]
+}
+
+// ReceiverAmt returns the amount forwarded to the final hop.
+func (r *Route) ReceiverAmt() lnwire.MilliSatoshi {
+	return r.FinalHop().AmtToForward
+}
+
+// TotalFees returns the total fees paid along the route.
+func (r *Route) TotalFees() lnwire.MilliSatoshi {
+	return r.TotalAmount - r.ReceiverAmt()
+}
diff --git a/payments/db/migration1/sql_converters.go b/payments/db/migration1/sql_converters.go
new file mode 100644
index 0000000..dbf88b2
--- /dev/null
+++ b/payments/db/migration1/sql_converters.go
@@ -0,0 +1,274 @@
+package migration1
+
+import (
+	"bytes"
+	"fmt"
+	"strconv"
+	"time"
+
+	"github.com/btcsuite/btcd/btcec/v2"
+	"github.com/lightningnetwork/lnd/lntypes"
+	"github.com/lightningnetwork/lnd/lnwire"
+	"github.com/lightningnetwork/lnd/payments/db/migration1/sqlc"
+	"github.com/lightningnetwork/lnd/record"
+	"github.com/lightningnetwork/lnd/tlv"
+)
+
+// dbPaymentToCreationInfo converts database payment data to the
+// PaymentCreationInfo struct.
+func dbPaymentToCreationInfo(paymentIdentifier []byte, amountMsat int64,
+	createdAt time.Time, intentPayload []byte,
+	firstHopCustomRecords lnwire.CustomRecords) *PaymentCreationInfo {
+
+	// This is the payment hash for non-AMP payments and the SetID for AMP
+	// payments.
+	var identifier lntypes.Hash
+	copy(identifier[:], paymentIdentifier)
+
+	return &PaymentCreationInfo{
+		PaymentIdentifier: identifier,
+		Value:             lnwire.MilliSatoshi(amountMsat),
+		// The creation time is stored in the database as UTC but here
+		// we convert it to local time.
+		CreationTime:          createdAt.Local(),
+		PaymentRequest:        intentPayload,
+		FirstHopCustomRecords: firstHopCustomRecords,
+	}
+}
+
+// dbAttemptToHTLCAttempt converts a database HTLC attempt to an HTLCAttempt.
+func dbAttemptToHTLCAttempt(dbAttempt sqlc.FetchHtlcAttemptsForPaymentsRow,
+	hops []sqlc.FetchHopsForAttemptsRow,
+	hopCustomRecords map[int64][]sqlc.PaymentHopCustomRecord,
+	routeCustomRecords []sqlc.PaymentAttemptFirstHopCustomRecord) (
+	*HTLCAttempt, error) {
+
+	// Convert route-level first hop custom records to CustomRecords map.
+	var firstHopWireCustomRecords lnwire.CustomRecords
+	if len(routeCustomRecords) > 0 {
+		firstHopWireCustomRecords = make(lnwire.CustomRecords)
+		for _, record := range routeCustomRecords {
+			firstHopWireCustomRecords[uint64(record.Key)] =
+				record.Value
+		}
+	}
+
+	// Build the route from the database data.
+	route, err := dbDataToRoute(
+		hops, hopCustomRecords, dbAttempt.FirstHopAmountMsat,
+		dbAttempt.RouteTotalTimeLock, dbAttempt.RouteTotalAmount,
+		dbAttempt.RouteSourceKey, firstHopWireCustomRecords,
+	)
+	if err != nil {
+		return nil, fmt.Errorf("failed to convert to route: %w",
+			err)
+	}
+
+	hash, err := lntypes.MakeHash(dbAttempt.PaymentHash)
+	if err != nil {
+		return nil, fmt.Errorf("failed to parse payment "+
+			"hash: %w", err)
+	}
+
+	// Create the attempt info.
+	var sessionKey [32]byte
+	copy(sessionKey[:], dbAttempt.SessionKey)
+
+	info := HTLCAttemptInfo{
+		AttemptID:   uint64(dbAttempt.AttemptIndex),
+		sessionKey:  sessionKey,
+		Route:       *route,
+		AttemptTime: dbAttempt.AttemptTime,
+		Hash:        &hash,
+	}
+
+	attempt := &HTLCAttempt{
+		HTLCAttemptInfo: info,
+	}
+
+	// If there's no resolution type, the attempt is still in-flight.
+	// Return early without processing settlement or failure info.
+	if !dbAttempt.ResolutionType.Valid {
+		return attempt, nil
+	}
+
+	// Add settlement info if present.
+	if HTLCAttemptResolutionType(dbAttempt.ResolutionType.Int32) ==
+		HTLCAttemptResolutionSettled {
+
+		var preimage lntypes.Preimage
+		copy(preimage[:], dbAttempt.SettlePreimage)
+
+		attempt.Settle = &HTLCSettleInfo{
+			Preimage:   preimage,
+			SettleTime: dbAttempt.ResolutionTime.Time,
+		}
+	}
+
+	// Add failure info if present.
+	if HTLCAttemptResolutionType(dbAttempt.ResolutionType.Int32) ==
+		HTLCAttemptResolutionFailed {
+
+		failure := &HTLCFailInfo{
+			FailTime: dbAttempt.ResolutionTime.Time,
+		}
+
+		if dbAttempt.HtlcFailReason.Valid {
+			failure.Reason = HTLCFailReason(
+				dbAttempt.HtlcFailReason.Int32,
+			)
+		}
+
+		if dbAttempt.FailureSourceIndex.Valid {
+			failure.FailureSourceIndex = uint32(
+				dbAttempt.FailureSourceIndex.Int32,
+			)
+		}
+
+		// Decode the failure message if present.
+		if len(dbAttempt.FailureMsg) > 0 {
+			msg, err := lnwire.DecodeFailureMessage(
+				bytes.NewReader(dbAttempt.FailureMsg), 0,
+			)
+			if err != nil {
+				return nil, fmt.Errorf("failed to decode "+
+					"failure message: %w", err)
+			}
+			failure.Message = msg
+		}
+
+		attempt.Failure = failure
+	}
+
+	return attempt, nil
+}
+
+// dbDataToRoute converts database route data to a Route.
+func dbDataToRoute(hops []sqlc.FetchHopsForAttemptsRow,
+	hopCustomRecords map[int64][]sqlc.PaymentHopCustomRecord,
+	firstHopAmountMsat int64, totalTimeLock int32, totalAmount int64,
+	sourceKey []byte, firstHopWireCustomRecords lnwire.CustomRecords) (
+	*Route, error) {
+
+	if len(hops) == 0 {
+		return nil, fmt.Errorf("no hops provided")
+	}
+
+	// Hops are already sorted by hop_index from the SQL query.
+	routeHops := make([]*Hop, len(hops))
+
+	for i, hop := range hops {
+		pubKey, err := NewVertexFromBytes(hop.PubKey)
+		if err != nil {
+			return nil, fmt.Errorf("failed to parse pub key: %w",
+				err)
+		}
+
+		var channelID uint64
+		if hop.Scid != "" {
+			// The SCID is stored as a string representation
+			// of the uint64.
+			var err error
+			channelID, err = strconv.ParseUint(hop.Scid, 10, 64)
+			if err != nil {
+				return nil, fmt.Errorf("failed to parse "+
+					"scid: %w", err)
+			}
+		}
+
+		routeHop := &Hop{
+			PubKeyBytes:      pubKey,
+			ChannelID:        channelID,
+			OutgoingTimeLock: uint32(hop.OutgoingTimeLock),
+			AmtToForward:     lnwire.MilliSatoshi(hop.AmtToForward),
+		}
+
+		// Add MPP record if present.
+		if len(hop.MppPaymentAddr) > 0 {
+			var paymentAddr [32]byte
+			copy(paymentAddr[:], hop.MppPaymentAddr)
+			routeHop.MPP = record.NewMPP(
+				lnwire.MilliSatoshi(hop.MppTotalMsat.Int64),
+				paymentAddr,
+			)
+		}
+
+		// Add AMP record if present.
+		if len(hop.AmpRootShare) > 0 {
+			var rootShare [32]byte
+			copy(rootShare[:], hop.AmpRootShare)
+			var setID [32]byte
+			copy(setID[:], hop.AmpSetID)
+
+			routeHop.AMP = record.NewAMP(
+				rootShare, setID,
+				uint32(hop.AmpChildIndex.Int32),
+			)
+		}
+
+		// Add blinding point if present (only for introduction node
+		// in blinded route).
+		if len(hop.BlindingPoint) > 0 {
+			pubKey, err := btcec.ParsePubKey(hop.BlindingPoint)
+			if err != nil {
+				return nil, fmt.Errorf("failed to parse "+
+					"blinding point: %w", err)
+			}
+			routeHop.BlindingPoint = pubKey
+		}
+
+		// Add encrypted data if present (for all blinded hops).
+		if len(hop.EncryptedData) > 0 {
+			routeHop.EncryptedData = hop.EncryptedData
+		}
+
+		// Add total amount if present (only for final hop in blinded
+		// route).
+		if hop.BlindedPathTotalAmt.Valid {
+			routeHop.TotalAmtMsat = lnwire.MilliSatoshi(
+				hop.BlindedPathTotalAmt.Int64,
+			)
+		}
+
+		// Add hop-level custom records.
+		if records, ok := hopCustomRecords[hop.ID]; ok {
+			routeHop.CustomRecords = make(
+				record.CustomSet,
+			)
+			for _, rec := range records {
+				routeHop.CustomRecords[uint64(rec.Key)] =
+					rec.Value
+			}
+		}
+
+		// Add metadata if present.
+		if len(hop.MetaData) > 0 {
+			routeHop.Metadata = hop.MetaData
+		}
+
+		routeHops[i] = routeHop
+	}
+
+	// Parse the source node public key.
+	var sourceNode Vertex
+	copy(sourceNode[:], sourceKey)
+
+	route := &Route{
+		TotalTimeLock:             uint32(totalTimeLock),
+		TotalAmount:               lnwire.MilliSatoshi(totalAmount),
+		SourcePubKey:              sourceNode,
+		Hops:                      routeHops,
+		FirstHopWireCustomRecords: firstHopWireCustomRecords,
+	}
+
+	// Set the first hop amount if it is set.
+	if firstHopAmountMsat != 0 {
+		route.FirstHopAmount = tlv.NewRecordT[tlv.TlvType0](
+			tlv.NewBigSizeT(lnwire.MilliSatoshi(
+				firstHopAmountMsat,
+			)),
+		)
+	}
+
+	return route, nil
+}
diff --git a/payments/db/migration1/sql_store.go b/payments/db/migration1/sql_store.go
new file mode 100644
index 0000000..0fe4aa1
--- /dev/null
+++ b/payments/db/migration1/sql_store.go
@@ -0,0 +1,2019 @@
+package migration1
+
+import (
+	"bytes"
+	"context"
+	"database/sql"
+	"errors"
+	"fmt"
+	"math"
+	"strconv"
+	"time"
+
+	"github.com/lightningnetwork/lnd/lntypes"
+	"github.com/lightningnetwork/lnd/lnwire"
+	"github.com/lightningnetwork/lnd/payments/db/migration1/sqlc"
+	"github.com/lightningnetwork/lnd/sqldb"
+)
+
+// PaymentIntentType represents the type of payment intent.
+type PaymentIntentType int16
+
+const (
+	// PaymentIntentTypeBolt11 indicates a BOLT11 invoice payment.
+	PaymentIntentTypeBolt11 PaymentIntentType = 0
+)
+
+// HTLCAttemptResolutionType represents the type of HTLC attempt resolution.
+type HTLCAttemptResolutionType int32
+
+const (
+	// HTLCAttemptResolutionSettled indicates the HTLC attempt was settled
+	// successfully with a preimage.
+	HTLCAttemptResolutionSettled HTLCAttemptResolutionType = 1
+
+	// HTLCAttemptResolutionFailed indicates the HTLC attempt failed.
+	HTLCAttemptResolutionFailed HTLCAttemptResolutionType = 2
+)
+
+// SQLQueries is a subset of the sqlc.Querier interface that can be used to
+// execute queries against the SQL payments tables.
+//
+//nolint:ll,interfacebloat
+type SQLQueries interface {
+	/*
+		Payment DB read operations.
+	*/
+	FilterPayments(ctx context.Context, query sqlc.FilterPaymentsParams) ([]sqlc.FilterPaymentsRow, error)
+	FilterPaymentsDesc(ctx context.Context, query sqlc.FilterPaymentsDescParams) ([]sqlc.FilterPaymentsDescRow, error)
+	FetchPayment(ctx context.Context, paymentIdentifier []byte) (sqlc.FetchPaymentRow, error)
+	FetchPaymentsByIDs(ctx context.Context, paymentIDs []int64) ([]sqlc.FetchPaymentsByIDsRow, error)
+
+	CountPayments(ctx context.Context) (int64, error)
+
+	FetchHtlcAttemptsForPayments(ctx context.Context, paymentIDs []int64) ([]sqlc.FetchHtlcAttemptsForPaymentsRow, error)
+	FetchHtlcAttemptResolutionsForPayments(ctx context.Context, paymentIDs []int64) ([]sqlc.FetchHtlcAttemptResolutionsForPaymentsRow, error)
+	FetchAllInflightAttempts(ctx context.Context, arg sqlc.FetchAllInflightAttemptsParams) ([]sqlc.PaymentHtlcAttempt, error)
+	FetchHopsForAttempts(ctx context.Context, htlcAttemptIndices []int64) ([]sqlc.FetchHopsForAttemptsRow, error)
+
+	FetchPaymentDuplicates(ctx context.Context, paymentID int64) ([]sqlc.PaymentDuplicate, error)
+
+	FetchPaymentLevelFirstHopCustomRecords(ctx context.Context, paymentIDs []int64) ([]sqlc.PaymentFirstHopCustomRecord, error)
+	FetchRouteLevelFirstHopCustomRecords(ctx context.Context, htlcAttemptIndices []int64) ([]sqlc.PaymentAttemptFirstHopCustomRecord, error)
+	FetchHopLevelCustomRecords(ctx context.Context, hopIDs []int64) ([]sqlc.PaymentHopCustomRecord, error)
+
+	/*
+		Payment DB write operations.
+	*/
+	InsertPaymentIntent(ctx context.Context, arg sqlc.InsertPaymentIntentParams) (int64, error)
+	InsertPayment(ctx context.Context, arg sqlc.InsertPaymentParams) (int64, error)
+	InsertPaymentFirstHopCustomRecord(ctx context.Context, arg sqlc.InsertPaymentFirstHopCustomRecordParams) error
+
+	InsertHtlcAttempt(ctx context.Context, arg sqlc.InsertHtlcAttemptParams) (int64, error)
+	InsertRouteHop(ctx context.Context, arg sqlc.InsertRouteHopParams) (int64, error)
+	InsertRouteHopMpp(ctx context.Context, arg sqlc.InsertRouteHopMppParams) error
+	InsertRouteHopAmp(ctx context.Context, arg sqlc.InsertRouteHopAmpParams) error
+	InsertRouteHopBlinded(ctx context.Context, arg sqlc.InsertRouteHopBlindedParams) error
+
+	InsertPaymentAttemptFirstHopCustomRecord(ctx context.Context, arg sqlc.InsertPaymentAttemptFirstHopCustomRecordParams) error
+	InsertPaymentHopCustomRecord(ctx context.Context, arg sqlc.InsertPaymentHopCustomRecordParams) error
+
+	SettleAttempt(ctx context.Context, arg sqlc.SettleAttemptParams) error
+	FailAttempt(ctx context.Context, arg sqlc.FailAttemptParams) error
+
+	FailPayment(ctx context.Context, arg sqlc.FailPaymentParams) (sql.Result, error)
+
+	DeletePayment(ctx context.Context, paymentID int64) error
+
+	// DeleteFailedAttempts removes all failed HTLCs from the db for a
+	// given payment.
+	DeleteFailedAttempts(ctx context.Context, paymentID int64) error
+
+	/*
+		Migration specific queries.
+
+		These queries are used ONLY for the one-time migration from KV
+		to SQL.
+	*/
+
+	// FetchPaymentsByIDsMig is a migration-only batch fetch that returns
+	// payment data along with HTLC attempt counts for structural
+	// validation.
+	FetchPaymentsByIDsMig(ctx context.Context, paymentIDs []int64) ([]sqlc.FetchPaymentsByIDsMigRow, error)
+
+	// InsertPaymentMig is a migration-only variant of InsertPayment that
+	// allows setting fail_reason when inserting historical payments, since
+	// for real payments they have not failed at creation time and so no
+	// failure reason would exist yet.
+	InsertPaymentMig(ctx context.Context, arg sqlc.InsertPaymentMigParams) (int64, error)
+
+	// InsertPaymentDuplicateMig inserts a duplicate payment record during
+	// migration.
+	InsertPaymentDuplicateMig(ctx context.Context, arg sqlc.InsertPaymentDuplicateMigParams) (int64, error)
+}
+
+// BatchedSQLQueries is a version of the SQLQueries that's capable
+// of batched database operations.
+type BatchedSQLQueries interface {
+	SQLQueries
+	sqldb.BatchedTx[SQLQueries]
+}
+
+// SQLStore represents a storage backend.
+type SQLStore struct {
+	cfg *SQLStoreConfig
+	db  BatchedSQLQueries
+}
+
+// A compile-time constraint to ensure SQLStore implements DB.
+var _ DB = (*SQLStore)(nil)
+
+// SQLStoreConfig holds the configuration for the SQLStore.
+type SQLStoreConfig struct {
+	// QueryConfig holds configuration values for SQL queries.
+	QueryCfg *sqldb.QueryConfig
+}
+
+// NewSQLStore creates a new SQLStore instance given an open
+// BatchedSQLPaymentsQueries storage backend.
+func NewSQLStore(cfg *SQLStoreConfig, db BatchedSQLQueries,
+	options ...OptionModifier) (*SQLStore, error) {
+
+	opts := DefaultOptions()
+	for _, applyOption := range options {
+		applyOption(opts)
+	}
+
+	if opts.NoMigration {
+		return nil, fmt.Errorf("the NoMigration option is not yet " +
+			"supported for SQL stores")
+	}
+
+	return &SQLStore{
+		cfg: cfg,
+		db:  db,
+	}, nil
+}
+
+// fetchPaymentWithCompleteData fetches a payment with all its related data
+// including attempts, hops, and custom records from the database.
+// This is a convenience wrapper around the batch loading functions for single
+// payment operations.
+func fetchPaymentWithCompleteData(ctx context.Context,
+	cfg *sqldb.QueryConfig, db SQLQueries,
+	dbPayment sqlc.PaymentAndIntent) (*MPPayment, error) {
+
+	payment := dbPayment.GetPayment()
+
+	// Load batch data for this single payment.
+	batchData, err := batchLoadPaymentDetailsData(
+		ctx, cfg, db, []int64{payment.ID},
+	)
+	if err != nil {
+		return nil, fmt.Errorf("failed to load batch data: %w", err)
+	}
+
+	// Build the payment from the batch data.
+	return buildPaymentFromBatchData(dbPayment, batchData)
+}
+
+// paymentsCompleteData holds the full payment data when batch loading base
+// payment data and all the related data for a payment.
+type paymentsCompleteData struct {
+	*paymentsBaseData
+	*paymentsDetailsData
+}
+
+// batchLoadPayments loads the full payment data for a batch of payment IDs.
+func batchLoadPayments(ctx context.Context, cfg *sqldb.QueryConfig,
+	db SQLQueries, paymentIDs []int64) (*paymentsCompleteData, error) {
+
+	baseData, err := batchLoadpaymentsBaseData(ctx, cfg, db, paymentIDs)
+	if err != nil {
+		return nil, fmt.Errorf("failed to load payment base data: %w",
+			err)
+	}
+
+	batchData, err := batchLoadPaymentDetailsData(ctx, cfg, db, paymentIDs)
+	if err != nil {
+		return nil, fmt.Errorf("failed to load payment batch data: %w",
+			err)
+	}
+
+	return &paymentsCompleteData{
+		paymentsBaseData:    baseData,
+		paymentsDetailsData: batchData,
+	}, nil
+}
+
+// paymentsBaseData holds the base payment and intent data for a batch of
+// payments.
+type paymentsBaseData struct {
+	// paymentsAndIntents maps payment ID to its payment and intent data.
+	paymentsAndIntents map[int64]sqlc.PaymentAndIntent
+}
+
+// batchLoadpaymentsBaseData loads the base payment and payment intent data for
+// a batch of payment IDs. This complements loadPaymentsBatchData which loads
+// related data (attempts, hops, custom records) but not the payment table
+// and payment intent table data.
+func batchLoadpaymentsBaseData(ctx context.Context,
+	cfg *sqldb.QueryConfig, db SQLQueries,
+	paymentIDs []int64) (*paymentsBaseData, error) {
+
+	baseData := &paymentsBaseData{
+		paymentsAndIntents: make(map[int64]sqlc.PaymentAndIntent),
+	}
+
+	if len(paymentIDs) == 0 {
+		return baseData, nil
+	}
+
+	err := sqldb.ExecuteBatchQuery(
+		ctx, cfg, paymentIDs,
+		func(id int64) int64 { return id },
+		func(ctx context.Context, ids []int64) (
+			[]sqlc.FetchPaymentsByIDsRow, error) {
+
+			records, err := db.FetchPaymentsByIDs(
+				ctx, ids,
+			)
+
+			return records, err
+		},
+		func(ctx context.Context,
+			payment sqlc.FetchPaymentsByIDsRow) error {
+
+			baseData.paymentsAndIntents[payment.ID] = payment
+
+			return nil
+		},
+	)
+	if err != nil {
+		return nil, fmt.Errorf("failed to fetch payment base "+
+			"data: %w", err)
+	}
+
+	return baseData, nil
+}
+
+// paymentsRelatedData holds all the batch-loaded data for multiple payments.
+// This does not include the base payment and intent data which is fetched
+// separately. It includes the additional data like attempts, hops, hop custom
+// records, and route custom records.
+type paymentsDetailsData struct {
+	// paymentCustomRecords maps payment ID to its custom records.
+	paymentCustomRecords map[int64][]sqlc.PaymentFirstHopCustomRecord
+
+	// attempts maps payment ID to its HTLC attempts.
+	attempts map[int64][]sqlc.FetchHtlcAttemptsForPaymentsRow
+
+	// hopsByAttempt maps attempt index to its hops.
+	hopsByAttempt map[int64][]sqlc.FetchHopsForAttemptsRow
+
+	// hopCustomRecords maps hop ID to its custom records.
+	hopCustomRecords map[int64][]sqlc.PaymentHopCustomRecord
+
+	// routeCustomRecords maps attempt index to its route-level custom
+	// records.
+	routeCustomRecords map[int64][]sqlc.PaymentAttemptFirstHopCustomRecord
+}
+
+// batchLoadPaymentCustomRecords loads payment-level custom records for a given
+// set of payment IDs. It uses a batch query to fetch all custom records for
+// the given payment IDs.
+func batchLoadPaymentCustomRecords(ctx context.Context,
+	cfg *sqldb.QueryConfig, db SQLQueries, paymentIDs []int64,
+	batchData *paymentsDetailsData) error {
+
+	return sqldb.ExecuteBatchQuery(
+		ctx, cfg, paymentIDs,
+		func(id int64) int64 { return id },
+		func(ctx context.Context, ids []int64) (
+			[]sqlc.PaymentFirstHopCustomRecord, error) {
+
+			//nolint:ll
+			records, err := db.FetchPaymentLevelFirstHopCustomRecords(
+				ctx, ids,
+			)
+
+			return records, err
+		},
+		func(ctx context.Context,
+			record sqlc.PaymentFirstHopCustomRecord) error {
+
+			paymentRecords :=
+				batchData.paymentCustomRecords[record.PaymentID]
+
+			batchData.paymentCustomRecords[record.PaymentID] =
+				append(paymentRecords, record)
+
+			return nil
+		},
+	)
+}
+
+// batchLoadHtlcAttempts loads HTLC attempts for all payments and returns all
+// attempt indices. It uses a batch query to fetch all attempts for the given
+// payment IDs.
+func batchLoadHtlcAttempts(ctx context.Context, cfg *sqldb.QueryConfig,
+	db SQLQueries, paymentIDs []int64,
+	batchData *paymentsDetailsData) ([]int64, error) {
+
+	var allAttemptIndices []int64
+
+	err := sqldb.ExecuteBatchQuery(
+		ctx, cfg, paymentIDs,
+		func(id int64) int64 { return id },
+		func(ctx context.Context, ids []int64) (
+			[]sqlc.FetchHtlcAttemptsForPaymentsRow, error) {
+
+			return db.FetchHtlcAttemptsForPayments(ctx, ids)
+		},
+		func(ctx context.Context,
+			attempt sqlc.FetchHtlcAttemptsForPaymentsRow) error {
+
+			batchData.attempts[attempt.PaymentID] = append(
+				batchData.attempts[attempt.PaymentID], attempt,
+			)
+			allAttemptIndices = append(
+				allAttemptIndices, attempt.AttemptIndex,
+			)
+
+			return nil
+		},
+	)
+
+	return allAttemptIndices, err
+}
+
+// batchLoadHopsForAttempts loads hops for all attempts and returns all hop IDs.
+// It uses a batch query to fetch all hops for the given attempt indices.
+func batchLoadHopsForAttempts(ctx context.Context, cfg *sqldb.QueryConfig,
+	db SQLQueries, attemptIndices []int64,
+	batchData *paymentsDetailsData) ([]int64, error) {
+
+	var hopIDs []int64
+
+	err := sqldb.ExecuteBatchQuery(
+		ctx, cfg, attemptIndices,
+		func(idx int64) int64 { return idx },
+		func(ctx context.Context, indices []int64) (
+			[]sqlc.FetchHopsForAttemptsRow, error) {
+
+			return db.FetchHopsForAttempts(ctx, indices)
+		},
+		func(ctx context.Context,
+			hop sqlc.FetchHopsForAttemptsRow) error {
+
+			attemptHops :=
+				batchData.hopsByAttempt[hop.HtlcAttemptIndex]
+
+			batchData.hopsByAttempt[hop.HtlcAttemptIndex] =
+				append(attemptHops, hop)
+
+			hopIDs = append(hopIDs, hop.ID)
+
+			return nil
+		},
+	)
+
+	return hopIDs, err
+}
+
+// batchLoadHopCustomRecords loads hop-level custom records for all hops. It
+// uses a batch query to fetch all custom records for the given hop IDs.
+func batchLoadHopCustomRecords(ctx context.Context, cfg *sqldb.QueryConfig,
+	db SQLQueries, hopIDs []int64, batchData *paymentsDetailsData) error {
+
+	return sqldb.ExecuteBatchQuery(
+		ctx, cfg, hopIDs,
+		func(id int64) int64 { return id },
+		func(ctx context.Context, ids []int64) (
+			[]sqlc.PaymentHopCustomRecord, error) {
+
+			return db.FetchHopLevelCustomRecords(ctx, ids)
+		},
+		func(ctx context.Context,
+			record sqlc.PaymentHopCustomRecord) error {
+
+			// TODO(ziggie): Can we get rid of this?
+			// This has to be in place otherwise the
+			// comparison will not match.
+			if record.Value == nil {
+				record.Value = []byte{}
+			}
+
+			batchData.hopCustomRecords[record.HopID] = append(
+				batchData.hopCustomRecords[record.HopID],
+				record,
+			)
+
+			return nil
+		},
+	)
+}
+
+// batchLoadRouteCustomRecords loads route-level first hop custom records for
+// all attempts. It uses a batch query to fetch all custom records for the given
+// attempt indices.
+func batchLoadRouteCustomRecords(ctx context.Context, cfg *sqldb.QueryConfig,
+	db SQLQueries, attemptIndices []int64,
+	batchData *paymentsDetailsData) error {
+
+	return sqldb.ExecuteBatchQuery(
+		ctx, cfg, attemptIndices,
+		func(idx int64) int64 { return idx },
+		func(ctx context.Context, indices []int64) (
+			[]sqlc.PaymentAttemptFirstHopCustomRecord, error) {
+
+			return db.FetchRouteLevelFirstHopCustomRecords(
+				ctx, indices,
+			)
+		},
+		func(ctx context.Context,
+			record sqlc.PaymentAttemptFirstHopCustomRecord) error {
+
+			idx := record.HtlcAttemptIndex
+			attemptRecords := batchData.routeCustomRecords[idx]
+
+			batchData.routeCustomRecords[idx] =
+				append(attemptRecords, record)
+
+			return nil
+		},
+	)
+}
+
+// paymentStatusData holds lightweight resolution data for computing
+// payment status efficiently during deletion operations.
+type paymentStatusData struct {
+	// resolutionTypes maps payment ID to a list of resolution types
+	// for that payment's HTLC attempts.
+	resolutionTypes map[int64][]sql.NullInt32
+}
+
+// batchLoadPaymentResolutions loads only HTLC resolution types for multiple
+// payments. This is a lightweight alternative to batchLoadPaymentsRelatedData
+// that's optimized for operations that only need to determine payment status.
+func batchLoadPaymentResolutions(ctx context.Context, cfg *sqldb.QueryConfig,
+	db SQLQueries, paymentIDs []int64) (*paymentStatusData, error) {
+
+	batchStatusData := &paymentStatusData{
+		resolutionTypes: make(map[int64][]sql.NullInt32),
+	}
+
+	if len(paymentIDs) == 0 {
+		return batchStatusData, nil
+	}
+
+	// Use a batch query to fetch all resolution types for the given payment
+	// IDs.
+	err := sqldb.ExecuteBatchQuery(
+		ctx, cfg, paymentIDs,
+		func(id int64) int64 { return id },
+		func(ctx context.Context, ids []int64) (
+			[]sqlc.FetchHtlcAttemptResolutionsForPaymentsRow,
+			error) {
+
+			return db.FetchHtlcAttemptResolutionsForPayments(
+				ctx, ids,
+			)
+		},
+		//nolint:ll
+		func(ctx context.Context,
+			res sqlc.FetchHtlcAttemptResolutionsForPaymentsRow) error {
+
+			// Group resolutions by payment ID.
+			batchStatusData.resolutionTypes[res.PaymentID] = append(
+				batchStatusData.resolutionTypes[res.PaymentID],
+				res.ResolutionType,
+			)
+
+			return nil
+		},
+	)
+	if err != nil {
+		return nil, fmt.Errorf("failed to fetch HTLC resolutions: %w",
+			err)
+	}
+
+	return batchStatusData, nil
+}
+
+// loadPaymentResolutions is a single-payment wrapper around
+// batchLoadPaymentResolutions for convenience and to prevent duplicate queries
+// so we reuse the same batch query for all payments.
+func loadPaymentResolutions(ctx context.Context, cfg *sqldb.QueryConfig,
+	db SQLQueries, paymentID int64) ([]sql.NullInt32, error) {
+
+	batchData, err := batchLoadPaymentResolutions(
+		ctx, cfg, db, []int64{paymentID},
+	)
+	if err != nil {
+		return nil, err
+	}
+
+	return batchData.resolutionTypes[paymentID], nil
+}
+
+// computePaymentStatusFromResolutions determines the payment status from
+// resolution types and failure reason without building the complete MPPayment
+// structure. This is a lightweight version that builds minimal HTLCAttempt
+// structures and delegates to decidePaymentStatus for consistency.
+func computePaymentStatusFromResolutions(resolutionTypes []sql.NullInt32,
+	failReason sql.NullInt32) (PaymentStatus, error) {
+
+	// Build minimal HTLCAttempt slice with only resolution info.
+	htlcs := make([]HTLCAttempt, len(resolutionTypes))
+	for i, resType := range resolutionTypes {
+		if !resType.Valid {
+			// NULL resolution_type means in-flight (no Settle, no
+			// Failure).
+			continue
+		}
+
+		switch HTLCAttemptResolutionType(resType.Int32) {
+		case HTLCAttemptResolutionSettled:
+			// Mark as settled (preimage details not needed for
+			// status).
+			htlcs[i].Settle = &HTLCSettleInfo{}
+
+		case HTLCAttemptResolutionFailed:
+			// Mark as failed (failure details not needed for
+			// status).
+			htlcs[i].Failure = &HTLCFailInfo{}
+
+		default:
+			return 0, fmt.Errorf("unknown resolution type: %v",
+				resType.Int32)
+		}
+	}
+
+	// Convert fail reason to FailureReason pointer.
+	var failureReason *FailureReason
+	if failReason.Valid {
+		reason := FailureReason(failReason.Int32)
+		failureReason = &reason
+	}
+
+	// Use the existing status decision logic.
+	return decidePaymentStatus(htlcs, failureReason)
+}
+
+// batchLoadPaymentDetailsData loads all related data for multiple payments in
+// batch. It uses a batch queries to fetch all data for the given payment IDs.
+func batchLoadPaymentDetailsData(ctx context.Context, cfg *sqldb.QueryConfig,
+	db SQLQueries, paymentIDs []int64) (*paymentsDetailsData, error) {
+
+	batchData := &paymentsDetailsData{
+		paymentCustomRecords: make(
+			map[int64][]sqlc.PaymentFirstHopCustomRecord,
+		),
+		attempts: make(
+			map[int64][]sqlc.FetchHtlcAttemptsForPaymentsRow,
+		),
+		hopsByAttempt: make(
+			map[int64][]sqlc.FetchHopsForAttemptsRow,
+		),
+		hopCustomRecords: make(
+			map[int64][]sqlc.PaymentHopCustomRecord,
+		),
+		routeCustomRecords: make(
+			map[int64][]sqlc.PaymentAttemptFirstHopCustomRecord,
+		),
+	}
+
+	if len(paymentIDs) == 0 {
+		return batchData, nil
+	}
+
+	// Load payment-level custom records.
+	err := batchLoadPaymentCustomRecords(
+		ctx, cfg, db, paymentIDs, batchData,
+	)
+	if err != nil {
+		return nil, fmt.Errorf("failed to fetch payment custom "+
+			"records: %w", err)
+	}
+
+	// Load HTLC attempts and collect attempt indices.
+	allAttemptIndices, err := batchLoadHtlcAttempts(
+		ctx, cfg, db, paymentIDs, batchData,
+	)
+	if err != nil {
+		return nil, fmt.Errorf("failed to fetch HTLC attempts: %w",
+			err)
+	}
+
+	if len(allAttemptIndices) == 0 {
+		// No attempts, return early.
+		return batchData, nil
+	}
+
+	// Load hops for all attempts and collect hop IDs.
+	hopIDs, err := batchLoadHopsForAttempts(
+		ctx, cfg, db, allAttemptIndices, batchData,
+	)
+	if err != nil {
+		return nil, fmt.Errorf("failed to fetch hops for attempts: %w",
+			err)
+	}
+
+	// Load hop-level custom records if there are any hops.
+	if len(hopIDs) > 0 {
+		err = batchLoadHopCustomRecords(ctx, cfg, db, hopIDs, batchData)
+		if err != nil {
+			return nil, fmt.Errorf("failed to fetch hop custom "+
+				"records: %w", err)
+		}
+	}
+
+	// Load route-level first hop custom records.
+	err = batchLoadRouteCustomRecords(
+		ctx, cfg, db, allAttemptIndices, batchData,
+	)
+	if err != nil {
+		return nil, fmt.Errorf("failed to fetch route custom "+
+			"records: %w", err)
+	}
+
+	return batchData, nil
+}
+
+// buildPaymentFromBatchData builds a complete MPPayment from a database payment
+// and pre-loaded batch data.
+func buildPaymentFromBatchData(dbPayment sqlc.PaymentAndIntent,
+	batchData *paymentsDetailsData) (*MPPayment, error) {
+
+	// The query will only return BOLT 11 payment intents or intents with
+	// no intent type set.
+	paymentIntent := dbPayment.GetPaymentIntent()
+	paymentRequest := paymentIntent.IntentPayload
+
+	payment := dbPayment.GetPayment()
+
+	// Get payment-level custom records from batch data.
+	customRecords := batchData.paymentCustomRecords[payment.ID]
+
+	// Convert to the FirstHopCustomRecords map.
+	var firstHopCustomRecords lnwire.CustomRecords
+	if len(customRecords) > 0 {
+		firstHopCustomRecords = make(lnwire.CustomRecords)
+		for _, record := range customRecords {
+			firstHopCustomRecords[uint64(record.Key)] = record.Value
+		}
+	}
+
+	// Convert database payment data to the PaymentCreationInfo struct.
+	info := dbPaymentToCreationInfo(
+		payment.PaymentIdentifier, payment.AmountMsat,
+		payment.CreatedAt, paymentRequest, firstHopCustomRecords,
+	)
+
+	// Get all HTLC attempts from batch data for a given payment.
+	dbAttempts := batchData.attempts[payment.ID]
+
+	// Convert all attempts to HTLCAttempt structs using the pre-loaded
+	// batch data.
+	attempts := make([]HTLCAttempt, 0, len(dbAttempts))
+	for _, dbAttempt := range dbAttempts {
+		attemptIndex := dbAttempt.AttemptIndex
+		// Convert the batch row type to the single row type.
+		attempt, err := dbAttemptToHTLCAttempt(
+			dbAttempt, batchData.hopsByAttempt[attemptIndex],
+			batchData.hopCustomRecords,
+			batchData.routeCustomRecords[attemptIndex],
+		)
+		if err != nil {
+			return nil, fmt.Errorf("failed to convert attempt "+
+				"%d: %w", attemptIndex, err)
+		}
+		attempts = append(attempts, *attempt)
+	}
+
+	// Set the failure reason if present.
+	//
+	// TODO(ziggie): Rename it to Payment Memo in the database?
+	var failureReason *FailureReason
+	if payment.FailReason.Valid {
+		reason := FailureReason(payment.FailReason.Int32)
+		failureReason = &reason
+	}
+
+	mpPayment := &MPPayment{
+		SequenceNum:   uint64(payment.ID),
+		Info:          info,
+		HTLCs:         attempts,
+		FailureReason: failureReason,
+	}
+
+	// The status and state will be determined by calling
+	// SetState after construction.
+	if err := mpPayment.SetState(); err != nil {
+		return nil, fmt.Errorf("failed to set payment state: %w", err)
+	}
+
+	return mpPayment, nil
+}
+
+// QueryPayments queries and retrieves payments from the database with support
+// for filtering, pagination, and efficient batch loading of related data.
+//
+// The function accepts a Query parameter that controls:
+//   - Pagination: IndexOffset specifies where to start (exclusive), and
+//     MaxPayments limits the number of results returned
+//   - Ordering: Reversed flag determines if results are returned in reverse
+//     chronological order
+//   - Filtering: CreationDateStart/End filter by creation time, and
+//     IncludeIncomplete controls whether non-succeeded payments are included
+//   - Metadata: CountTotal flag determines if the total payment count should
+//     be calculated
+//
+// The function optimizes performance by loading all related data (HTLCs,
+// sequences, failure reasons, etc.) for multiple payments in a single batch
+// query, rather than fetching each payment's data individually.
+//
+// Returns a Response containing:
+//   - Payments: the list of matching payments with complete data
+//   - FirstIndexOffset/LastIndexOffset: pagination cursors for the first and
+//     last payment in the result set
+//   - TotalCount: total number of payments in the database (if CountTotal was
+//     requested, otherwise 0)
+//
+// This is part of the DB interface.
+func (s *SQLStore) QueryPayments(ctx context.Context, query Query) (Response,
+	error) {
+
+	if query.MaxPayments == 0 {
+		return Response{}, fmt.Errorf("max payments must be non-zero")
+	}
+
+	var (
+		allPayments   []*MPPayment
+		totalCount    int64
+		initialCursor int64
+	)
+
+	extractCursor := func(row sqlc.FilterPaymentsRow) int64 {
+		return row.Payment.ID
+	}
+
+	err := s.db.ExecTx(ctx, sqldb.ReadTxOpt(), func(db SQLQueries) error {
+		// We first count all payments to determine the total count
+		// if requested.
+		if query.CountTotal {
+			totalPayments, err := db.CountPayments(ctx)
+			if err != nil {
+				return fmt.Errorf("failed to count "+
+					"payments: %w", err)
+			}
+			totalCount = totalPayments
+		}
+
+		// collectFunc extracts the payment ID from each payment row.
+		collectFunc := func(row sqlc.FilterPaymentsRow) (int64, error) {
+			return row.Payment.ID, nil
+		}
+
+		// batchDataFunc loads all related data for a batch of payments.
+		batchDataFunc := func(ctx context.Context, paymentIDs []int64) (
+			*paymentsDetailsData, error) {
+
+			return batchLoadPaymentDetailsData(
+				ctx, s.cfg.QueryCfg, db, paymentIDs,
+			)
+		}
+
+		// processPayment processes each payment with the batch-loaded
+		// data.
+		processPayment := func(ctx context.Context,
+			dbPayment sqlc.FilterPaymentsRow,
+			batchData *paymentsDetailsData) error {
+
+			// Build the payment from the pre-loaded batch data.
+			mpPayment, err := buildPaymentFromBatchData(
+				dbPayment, batchData,
+			)
+			if err != nil {
+				return fmt.Errorf("failed to fetch payment "+
+					"with complete data: %w", err)
+			}
+
+			// To keep compatibility with the old API, we only
+			// return non-succeeded payments if requested.
+			if mpPayment.Status != StatusSucceeded &&
+				!query.IncludeIncomplete {
+
+				return nil
+			}
+
+			if uint64(len(allPayments)) >= query.MaxPayments {
+				return errMaxPaymentsReached
+			}
+
+			allPayments = append(allPayments, mpPayment)
+
+			return nil
+		}
+
+		//nolint:ll
+		convertFilterPaymentsDescRows := func(
+			rows []sqlc.FilterPaymentsDescRow) []sqlc.FilterPaymentsRow {
+
+			out := make([]sqlc.FilterPaymentsRow, len(rows))
+			for i, row := range rows {
+				out[i] = sqlc.FilterPaymentsRow{
+					Payment:       row.Payment,
+					IntentType:    row.IntentType,
+					IntentPayload: row.IntentPayload,
+				}
+			}
+
+			return out
+		}
+
+		queryFunc := func(ctx context.Context, lastID int64,
+			limit int32) ([]sqlc.FilterPaymentsRow, error) {
+
+			// Default date bounds: epoch start and far
+			// future. These are always provided so the SQL
+			// query uses simple comparisons instead of
+			// COALESCE (which causes type mismatch on
+			// Postgres) or OR-based optional filters (which
+			// can prevent index usage).
+			createdAfter := time.Unix(0, 0).UTC()
+			if query.CreationDateStart != 0 {
+				createdAfter = time.Unix(
+					query.CreationDateStart, 0,
+				).UTC()
+			}
+
+			createdBefore := time.Date(
+				9999, 12, 31, 23, 59, 59, 0, time.UTC,
+			)
+			if query.CreationDateEnd != 0 {
+				createdBefore = time.Unix(
+					query.CreationDateEnd, 0,
+				).UTC()
+			}
+
+			filterParams := sqlc.FilterPaymentsParams{
+				NumLimit:      limit,
+				CreatedAfter:  createdAfter,
+				CreatedBefore: createdBefore,
+				// For now there only BOLT 11 payment intents
+				// exist.
+				IntentType: sqldb.SQLInt16(
+					PaymentIntentTypeBolt11,
+				),
+			}
+
+			if query.Reversed {
+				filterParams.IndexOffsetLet = sqldb.SQLInt64(
+					lastID,
+				)
+			} else {
+				filterParams.IndexOffsetGet = sqldb.SQLInt64(
+					lastID,
+				)
+			}
+
+			if query.Reversed {
+				rows, err := db.FilterPaymentsDesc(
+					ctx, sqlc.FilterPaymentsDescParams(
+						filterParams,
+					),
+				)
+				if err != nil {
+					return nil, err
+				}
+
+				return convertFilterPaymentsDescRows(rows), nil
+			}
+
+			return db.FilterPayments(ctx, filterParams)
+		}
+
+		if query.Reversed {
+			if query.IndexOffset == 0 {
+				initialCursor = int64(math.MaxInt64)
+			} else {
+				initialCursor = int64(query.IndexOffset)
+			}
+		} else {
+			initialCursor = int64(query.IndexOffset)
+		}
+
+		return sqldb.ExecuteCollectAndBatchWithSharedDataQuery(
+			ctx, s.cfg.QueryCfg, initialCursor, queryFunc,
+			extractCursor, collectFunc, batchDataFunc,
+			processPayment,
+		)
+	}, func() {
+		allPayments = nil
+	})
+
+	// We make sure we don't return an error if we reached the maximum
+	// number of payments. Which is the pagination limit for the query
+	// itself.
+	if err != nil && !errors.Is(err, errMaxPaymentsReached) {
+		return Response{}, fmt.Errorf("failed to query payments: %w",
+			err)
+	}
+
+	// Handle case where no payments were found
+	if len(allPayments) == 0 {
+		return Response{
+			Payments:         allPayments,
+			FirstIndexOffset: 0,
+			LastIndexOffset:  0,
+			TotalCount:       uint64(totalCount),
+		}, nil
+	}
+
+	// If the query was reversed, we need to reverse the payment list
+	// to match the kvstore behavior and return payments in forward order.
+	if query.Reversed {
+		for i, j := 0, len(allPayments)-1; i < j; i, j = i+1, j-1 {
+			allPayments[i], allPayments[j] = allPayments[j],
+				allPayments[i]
+		}
+	}
+
+	return Response{
+		Payments:         allPayments,
+		FirstIndexOffset: allPayments[0].SequenceNum,
+		LastIndexOffset:  allPayments[len(allPayments)-1].SequenceNum,
+		TotalCount:       uint64(totalCount),
+	}, nil
+}
+
+// fetchPaymentByHash fetches a payment by its hash from the database. It is a
+// convenience wrapper around the FetchPayment method and checks for
+// no rows error and returns ErrPaymentNotInitiated if no payment is found.
+func fetchPaymentByHash(ctx context.Context, db SQLQueries,
+	paymentHash lntypes.Hash) (sqlc.FetchPaymentRow, error) {
+
+	dbPayment, err := db.FetchPayment(ctx, paymentHash[:])
+	if err != nil && !errors.Is(err, sql.ErrNoRows) {
+		return dbPayment, fmt.Errorf("failed to fetch payment: %w", err)
+	}
+
+	if errors.Is(err, sql.ErrNoRows) {
+		return dbPayment, ErrPaymentNotInitiated
+	}
+
+	return dbPayment, nil
+}
+
+// FetchPayment retrieves a complete payment record from the database by its
+// payment hash. The returned MPPayment includes all payment metadata such as
+// creation info, payment status, current state, all HTLC attempts (both
+// successful and failed), and the failure reason if the payment has been
+// marked as failed.
+//
+// Returns ErrPaymentNotInitiated if no payment with the given hash exists.
+//
+// This is part of the DB interface.
+func (s *SQLStore) FetchPayment(ctx context.Context,
+	paymentHash lntypes.Hash) (*MPPayment, error) {
+
+	var mpPayment *MPPayment
+
+	err := s.db.ExecTx(ctx, sqldb.ReadTxOpt(), func(db SQLQueries) error {
+		dbPayment, err := fetchPaymentByHash(ctx, db, paymentHash)
+		if err != nil {
+			return err
+		}
+
+		mpPayment, err = fetchPaymentWithCompleteData(
+			ctx, s.cfg.QueryCfg, db, dbPayment,
+		)
+		if err != nil {
+			return fmt.Errorf("failed to fetch payment with "+
+				"complete data: %w", err)
+		}
+
+		return nil
+	}, sqldb.NoOpReset)
+	if err != nil {
+		return nil, err
+	}
+
+	return mpPayment, nil
+}
+
+// FetchInFlightPayments retrieves all payments that have HTLC attempts
+// currently in flight (not yet settled or failed). These are payments with at
+// least one HTLC attempt that has been registered but has no resolution record.
+//
+// The SQLStore implementation provides a significant performance improvement
+// over the KVStore implementation by using targeted SQL queries instead of
+// scanning all payments.
+//
+// This method is part of the PaymentReader interface, which is embedded in the
+// DB interface. It's typically called during node startup to resume monitoring
+// of pending payments and ensure HTLCs are properly tracked.
+//
+// TODO(ziggie): Consider changing the interface to use a callback or iterator
+// pattern instead of returning all payments at once. This would allow
+// processing payments one at a time without holding them all in memory
+// simultaneously:
+//   - Callback: func FetchInFlightPayments(ctx, func(*MPPayment) error) error
+//   - Iterator: func FetchInFlightPayments(ctx) (PaymentIterator, error)
+//
+// While inflight payments are typically a small subset, this would improve
+// memory efficiency for nodes with unusually high numbers of concurrent
+// payments and would better leverage the existing pagination infrastructure.
+func (s *SQLStore) FetchInFlightPayments(ctx context.Context) ([]*MPPayment,
+	error) {
+
+	var mpPayments []*MPPayment
+
+	err := s.db.ExecTx(ctx, sqldb.ReadTxOpt(), func(db SQLQueries) error {
+		// Track which payment IDs we've already processed across all
+		// pages to avoid loading the same payment multiple times when
+		// multiple inflight attempts belong to the same payment.
+		processedPayments := make(map[int64]*MPPayment)
+
+		extractCursor := func(row sqlc.PaymentHtlcAttempt) int64 {
+			return row.AttemptIndex
+		}
+
+		// collectFunc extracts the payment ID from each attempt row.
+		collectFunc := func(row sqlc.PaymentHtlcAttempt) (
+			int64, error) {
+
+			return row.PaymentID, nil
+		}
+
+		// batchDataFunc loads payment data for a batch of payment IDs,
+		// but only for IDs we haven't processed yet.
+		batchDataFunc := func(ctx context.Context,
+			paymentIDs []int64) (*paymentsCompleteData, error) {
+
+			// Filter out already-processed payment IDs.
+			uniqueIDs := make([]int64, 0, len(paymentIDs))
+			for _, id := range paymentIDs {
+				_, processed := processedPayments[id]
+				if !processed {
+					uniqueIDs = append(uniqueIDs, id)
+				}
+			}
+
+			// If uniqueIDs is empty, the batch load will return
+			// empty batch data.
+			return batchLoadPayments(
+				ctx, s.cfg.QueryCfg, db, uniqueIDs,
+			)
+		}
+
+		// processAttempt processes each attempt. We only build and
+		// store the payment once per unique payment ID.
+		processAttempt := func(ctx context.Context,
+			row sqlc.PaymentHtlcAttempt,
+			batchData *paymentsCompleteData) error {
+
+			// Skip if we've already processed this payment.
+			_, processed := processedPayments[row.PaymentID]
+			if processed {
+				return nil
+			}
+
+			dbPayment := batchData.paymentsAndIntents[row.PaymentID]
+
+			// Build the payment from batch data.
+			mpPayment, err := buildPaymentFromBatchData(
+				dbPayment, batchData.paymentsDetailsData,
+			)
+			if err != nil {
+				return fmt.Errorf("failed to build payment: %w",
+					err)
+			}
+
+			// Store in our processed map.
+			processedPayments[row.PaymentID] = mpPayment
+
+			return nil
+		}
+
+		queryFunc := func(ctx context.Context, lastAttemptIndex int64,
+			limit int32) ([]sqlc.PaymentHtlcAttempt,
+			error) {
+
+			return db.FetchAllInflightAttempts(ctx,
+				sqlc.FetchAllInflightAttemptsParams{
+					AttemptIndex: lastAttemptIndex,
+					Limit:        limit,
+				},
+			)
+		}
+
+		err := sqldb.ExecuteCollectAndBatchWithSharedDataQuery(
+			ctx, s.cfg.QueryCfg, int64(-1), queryFunc,
+			extractCursor, collectFunc, batchDataFunc,
+			processAttempt,
+		)
+		if err != nil {
+			return err
+		}
+
+		// Convert map to slice.
+		mpPayments = make([]*MPPayment, 0, len(processedPayments))
+		for _, payment := range processedPayments {
+			mpPayments = append(mpPayments, payment)
+		}
+
+		return nil
+	}, func() {
+		mpPayments = nil
+	})
+	if err != nil {
+		return nil, fmt.Errorf("failed to fetch inflight "+
+			"payments: %w", err)
+	}
+
+	return mpPayments, nil
+}
+
+// DeleteFailedAttempts removes all failed HTLC attempts from the database for
+// the specified payment, while preserving the payment record itself and any
+// successful or in-flight attempts.
+//
+// The method performs the following validations before deletion:
+//   - StatusInitiated: Can delete failed attempts
+//   - StatusInFlight: Cannot delete, returns ErrPaymentInFlight (active HTLCs
+//     still on the network)
+//   - StatusSucceeded: Can delete failed attempts (payment completed)
+//   - StatusFailed: Can delete failed attempts (payment permanently failed)
+//
+// This method is idempotent - calling it multiple times on the same payment
+// has no adverse effects.
+//
+// This method is part of the PaymentControl interface, which is embedded in
+// the PaymentWriter interface and ultimately the DB interface. It represents
+// the final step (step 5) in the payment lifecycle control flow and should be
+// called after a payment reaches a terminal state (succeeded or permanently
+// failed) to clean up historical failed attempts.
+func (s *SQLStore) DeleteFailedAttempts(ctx context.Context,
+	paymentHash lntypes.Hash) error {
+
+	err := s.db.ExecTx(ctx, sqldb.WriteTxOpt(), func(db SQLQueries) error {
+		dbPayment, err := fetchPaymentByHash(ctx, db, paymentHash)
+		if err != nil {
+			return err
+		}
+
+		paymentStatus, err := computePaymentStatusFromDB(
+			ctx, s.cfg.QueryCfg, db, dbPayment,
+		)
+		if err != nil {
+			return fmt.Errorf("failed to compute payment "+
+				"status: %w", err)
+		}
+
+		if err := paymentStatus.removable(); err != nil {
+			return fmt.Errorf("cannot delete failed "+
+				"attempts for payment %v: %w", paymentHash, err)
+		}
+
+		// Then we delete the failed attempts for this payment.
+		return db.DeleteFailedAttempts(ctx, dbPayment.GetPayment().ID)
+	}, sqldb.NoOpReset)
+	if err != nil {
+		return fmt.Errorf("failed to delete failed attempts for "+
+			"payment %v: %w", paymentHash, err)
+	}
+
+	return nil
+}
+
+// computePaymentStatusFromDB computes the payment status by fetching minimal
+// data from the database. This is a lightweight query optimized for SQL that
+// doesn't load route data, making it significantly more efficient than
+// FetchPayment when only the status is needed.
+func computePaymentStatusFromDB(ctx context.Context, cfg *sqldb.QueryConfig,
+	db SQLQueries, dbPayment sqlc.PaymentAndIntent) (PaymentStatus, error) {
+
+	payment := dbPayment.GetPayment()
+
+	// Load the resolution types for the payment.
+	resolutionTypes, err := loadPaymentResolutions(
+		ctx, cfg, db, payment.ID,
+	)
+	if err != nil {
+		return 0, fmt.Errorf("failed to load payment resolutions: %w",
+			err)
+	}
+
+	// Use the lightweight status computation.
+	status, err := computePaymentStatusFromResolutions(
+		resolutionTypes, payment.FailReason,
+	)
+	if err != nil {
+		return 0, fmt.Errorf("failed to compute payment status: %w",
+			err)
+	}
+
+	return status, nil
+}
+
+// DeletePayment removes a payment or its failed HTLC attempts from the
+// database based on the failedAttemptsOnly flag.
+//
+// If failedAttemptsOnly is true, this method deletes only the failed HTLC
+// attempts for the payment while preserving the payment record itself and any
+// successful or in-flight attempts. This is useful for cleaning up historical
+// failed attempts after a payment reaches a terminal state.
+//
+// If failedAttemptsOnly is false, this method deletes the entire payment
+// record including all payment metadata, payment creation info, all HTLC
+// attempts (both failed and successful), and associated data such as payment
+// intents and custom records.
+//
+// Before deletion, this method validates the payment status to ensure it's
+// safe to delete:
+//   - StatusInitiated: Can be deleted (no HTLCs sent yet)
+//   - StatusInFlight: Cannot be deleted, returns ErrPaymentInFlight (active
+//     HTLCs on the network)
+//   - StatusSucceeded: Can be deleted (payment completed successfully)
+//   - StatusFailed: Can be deleted (payment has failed permanently)
+//
+// Returns an error if the payment has in-flight HTLCs or if the payment
+// doesn't exist.
+//
+// This method is part of the PaymentWriter interface, which is embedded in
+// the DB interface.
+func (s *SQLStore) DeletePayment(ctx context.Context, paymentHash lntypes.Hash,
+	failedHtlcsOnly bool) error {
+
+	err := s.db.ExecTx(ctx, sqldb.WriteTxOpt(), func(db SQLQueries) error {
+		dbPayment, err := fetchPaymentByHash(ctx, db, paymentHash)
+		if err != nil {
+			return err
+		}
+
+		paymentStatus, err := computePaymentStatusFromDB(
+			ctx, s.cfg.QueryCfg, db, dbPayment,
+		)
+		if err != nil {
+			return fmt.Errorf("failed to compute payment "+
+				"status: %w", err)
+		}
+
+		if err := paymentStatus.removable(); err != nil {
+			return fmt.Errorf("payment %v cannot be deleted: %w",
+				paymentHash, err)
+		}
+
+		// If we are only deleting failed HTLCs, we delete them.
+		if failedHtlcsOnly {
+			return db.DeleteFailedAttempts(
+				ctx, dbPayment.GetPayment().ID,
+			)
+		}
+
+		// In case we are not deleting failed HTLCs, we delete the
+		// payment which will cascade delete all related data.
+		return db.DeletePayment(ctx, dbPayment.GetPayment().ID)
+	}, sqldb.NoOpReset)
+	if err != nil {
+		return fmt.Errorf("failed to delete failed attempts for "+
+			"payment %v: %w", paymentHash, err)
+	}
+
+	return nil
+}
+
+// InitPayment creates a new payment record in the database with the given
+// payment hash and creation info.
+//
+// Before creating the payment, this method checks if a payment with the same
+// hash already exists and validates whether initialization is allowed based on
+// the existing payment's status:
+//   - StatusInitiated: Returns ErrPaymentExists (payment already created,
+//     HTLCs may be in flight)
+//   - StatusInFlight: Returns ErrPaymentInFlight (payment currently being
+//     attempted)
+//   - StatusSucceeded: Returns ErrAlreadyPaid (payment already succeeded)
+//   - StatusFailed: Allows retry by deleting the old payment record and
+//     creating a new one
+//
+// If no existing payment is found, a new payment record is created with
+// StatusInitiated and stored with all associated metadata.
+//
+// This method is part of the PaymentControl interface, which is embedded in
+// the PaymentWriter interface and ultimately the DB interface, representing
+// the first step in the payment lifecycle control flow.
+func (s *SQLStore) InitPayment(ctx context.Context, paymentHash lntypes.Hash,
+	paymentCreationInfo *PaymentCreationInfo) error {
+
+	// Create the payment in the database.
+	err := s.db.ExecTx(ctx, sqldb.WriteTxOpt(), func(db SQLQueries) error {
+		existingPayment, err := db.FetchPayment(ctx, paymentHash[:])
+		switch {
+		// A payment with this hash already exists. We need to check its
+		// status to see if we can re-initialize.
+		case err == nil:
+			paymentStatus, err := computePaymentStatusFromDB(
+				ctx, s.cfg.QueryCfg, db, existingPayment,
+			)
+			if err != nil {
+				return fmt.Errorf("failed to compute payment "+
+					"status: %w", err)
+			}
+
+			// Check if the payment is initializable otherwise
+			// we'll return early.
+			if err := paymentStatus.initializable(); err != nil {
+				return fmt.Errorf("payment is not "+
+					"initializable: %w", err)
+			}
+
+			// If the initializable check above passes, then the
+			// existing payment has failed. So we delete it and
+			// all of its previous artifacts. We rely on
+			// cascading deletes to clean up the rest.
+			err = db.DeletePayment(ctx, existingPayment.Payment.ID)
+			if err != nil {
+				return fmt.Errorf("failed to delete "+
+					"payment: %w", err)
+			}
+
+		// An unexpected error occurred while fetching the payment.
+		case !errors.Is(err, sql.ErrNoRows):
+			// Some other error occurred
+			return fmt.Errorf("failed to check existing "+
+				"payment: %w", err)
+
+		// The payment does not yet exist, so we can proceed.
+		default:
+		}
+
+		// Insert the payment first to get its ID.
+		paymentID, err := db.InsertPayment(
+			ctx, sqlc.InsertPaymentParams{
+				AmountMsat: int64(
+					paymentCreationInfo.Value,
+				),
+				CreatedAt: paymentCreationInfo.
+					CreationTime.UTC(),
+				PaymentIdentifier: paymentHash[:],
+			},
+		)
+		if err != nil {
+			return fmt.Errorf("failed to insert payment: %w", err)
+		}
+
+		// If there's a payment request, insert the payment intent.
+		if len(paymentCreationInfo.PaymentRequest) > 0 {
+			_, err = db.InsertPaymentIntent(
+				ctx, sqlc.InsertPaymentIntentParams{
+					PaymentID: paymentID,
+					IntentType: int16(
+						PaymentIntentTypeBolt11,
+					),
+					IntentPayload: paymentCreationInfo.
+						PaymentRequest,
+				},
+			)
+			if err != nil {
+				return fmt.Errorf("failed to insert "+
+					"payment intent: %w", err)
+			}
+		}
+
+		firstHopCustomRecords := paymentCreationInfo.
+			FirstHopCustomRecords
+
+		for key, value := range firstHopCustomRecords {
+			err = db.InsertPaymentFirstHopCustomRecord(
+				ctx,
+				sqlc.InsertPaymentFirstHopCustomRecordParams{
+					PaymentID: paymentID,
+					Key:       int64(key),
+					Value:     value,
+				},
+			)
+			if err != nil {
+				return fmt.Errorf("failed to insert "+
+					"payment first hop custom "+
+					"record: %w", err)
+			}
+		}
+
+		return nil
+	}, sqldb.NoOpReset)
+	if err != nil {
+		return fmt.Errorf("failed to initialize payment: %w", err)
+	}
+
+	return nil
+}
+
+// insertRouteHops inserts all route hop data for a given set of hops.
+func (s *SQLStore) insertRouteHops(ctx context.Context, db SQLQueries,
+	hops []*Hop, attemptID uint64) error {
+
+	for i, hop := range hops {
+		// Insert the basic route hop data and get the generated ID.
+		hopID, err := db.InsertRouteHop(ctx, sqlc.InsertRouteHopParams{
+			HtlcAttemptIndex: int64(attemptID),
+			HopIndex:         int32(i),
+			PubKey:           hop.PubKeyBytes[:],
+			Scid: strconv.FormatUint(
+				hop.ChannelID, 10,
+			),
+			OutgoingTimeLock: int32(hop.OutgoingTimeLock),
+			AmtToForward:     int64(hop.AmtToForward),
+			MetaData:         hop.Metadata,
+		})
+		if err != nil {
+			return fmt.Errorf("failed to insert route hop: %w", err)
+		}
+
+		// Insert the per-hop custom records.
+		if len(hop.CustomRecords) > 0 {
+			for key, value := range hop.CustomRecords {
+				err = db.InsertPaymentHopCustomRecord(
+					ctx,
+					sqlc.InsertPaymentHopCustomRecordParams{
+						HopID: hopID,
+						Key:   int64(key),
+						Value: value,
+					})
+				if err != nil {
+					return fmt.Errorf("failed to insert "+
+						"payment hop custom record: %w",
+						err)
+				}
+			}
+		}
+
+		// Insert MPP data if present.
+		if hop.MPP != nil {
+			paymentAddr := hop.MPP.PaymentAddr()
+			err = db.InsertRouteHopMpp(
+				ctx, sqlc.InsertRouteHopMppParams{
+					HopID:       hopID,
+					PaymentAddr: paymentAddr[:],
+					TotalMsat:   int64(hop.MPP.TotalMsat()),
+				})
+			if err != nil {
+				return fmt.Errorf("failed to insert "+
+					"route hop MPP: %w", err)
+			}
+		}
+
+		// Insert AMP data if present.
+		if hop.AMP != nil {
+			rootShare := hop.AMP.RootShare()
+			setID := hop.AMP.SetID()
+			err = db.InsertRouteHopAmp(
+				ctx, sqlc.InsertRouteHopAmpParams{
+					HopID:      hopID,
+					RootShare:  rootShare[:],
+					SetID:      setID[:],
+					ChildIndex: int32(hop.AMP.ChildIndex()),
+				})
+			if err != nil {
+				return fmt.Errorf("failed to insert "+
+					"route hop AMP: %w", err)
+			}
+		}
+
+		// Insert blinded route data if present. Every hop in the
+		// blinded path must have an encrypted data record. If the
+		// encrypted data is not present, we skip the insertion.
+		if hop.EncryptedData == nil {
+			continue
+		}
+
+		// The introduction point has a blinding point set.
+		var blindingPointBytes []byte
+		if hop.BlindingPoint != nil {
+			blindingPointBytes = hop.BlindingPoint.
+				SerializeCompressed()
+		}
+
+		// The total amount is only set for the final hop in a
+		// blinded path.
+		totalAmtMsat := sql.NullInt64{}
+		if i == len(hops)-1 {
+			totalAmtMsat = sql.NullInt64{
+				Int64: int64(hop.TotalAmtMsat),
+				Valid: true,
+			}
+		}
+
+		err = db.InsertRouteHopBlinded(ctx,
+			sqlc.InsertRouteHopBlindedParams{
+				HopID:               hopID,
+				EncryptedData:       hop.EncryptedData,
+				BlindingPoint:       blindingPointBytes,
+				BlindedPathTotalAmt: totalAmtMsat,
+			},
+		)
+		if err != nil {
+			return fmt.Errorf("failed to insert "+
+				"route hop blinded: %w", err)
+		}
+	}
+
+	return nil
+}
+
+// RegisterAttempt atomically records a new HTLC attempt for the specified
+// payment. The attempt includes the attempt ID, session key, route information
+// (hops, timelocks, amounts), and optional data such as MPP/AMP parameters,
+// blinded route data, and custom records.
+//
+// Returns the updated MPPayment with the new attempt appended to the HTLCs
+// slice, and the payment state recalculated. Returns an error if the payment
+// doesn't exist or validation fails.
+//
+// This method is part of the PaymentControl interface, which is embedded in
+// the PaymentWriter interface and ultimately the DB interface. It represents
+// step 2 in the payment lifecycle control flow, called after InitPayment and
+// potentially multiple times for multi-path payments.
+func (s *SQLStore) RegisterAttempt(ctx context.Context,
+	paymentHash lntypes.Hash, attempt *HTLCAttemptInfo) (*MPPayment,
+	error) {
+
+	var mpPayment *MPPayment
+
+	err := s.db.ExecTx(ctx, sqldb.WriteTxOpt(), func(db SQLQueries) error {
+		// Make sure the payment exists.
+		dbPayment, err := db.FetchPayment(ctx, paymentHash[:])
+		if err != nil {
+			return err
+		}
+
+		// We fetch the complete payment to determine if the payment is
+		// registrable.
+		//
+		// TODO(ziggie): We could improve the query here since only
+		// the last hop data is needed here not the complete payment
+		// data.
+		mpPayment, err = fetchPaymentWithCompleteData(
+			ctx, s.cfg.QueryCfg, db, dbPayment,
+		)
+		if err != nil {
+			return fmt.Errorf("failed to fetch payment with "+
+				"complete data: %w", err)
+		}
+
+		if err := mpPayment.Registrable(); err != nil {
+			return fmt.Errorf("htlc attempt not registrable: %w",
+				err)
+		}
+
+		// Verify the attempt is compatible with the existing payment.
+		if err := verifyAttempt(mpPayment, attempt); err != nil {
+			return fmt.Errorf("failed to verify attempt: %w", err)
+		}
+
+		// Register the plain HTLC attempt next.
+		sessionKey := attempt.SessionKey()
+		sessionKeyBytes := sessionKey.Serialize()
+
+		_, err = db.InsertHtlcAttempt(ctx, sqlc.InsertHtlcAttemptParams{
+			PaymentID:    dbPayment.Payment.ID,
+			AttemptIndex: int64(attempt.AttemptID),
+			SessionKey:   sessionKeyBytes,
+			AttemptTime:  attempt.AttemptTime,
+			PaymentHash:  paymentHash[:],
+			FirstHopAmountMsat: int64(
+				attempt.Route.FirstHopAmount.Val.Int(),
+			),
+			RouteTotalTimeLock: int32(attempt.Route.TotalTimeLock),
+			RouteTotalAmount:   int64(attempt.Route.TotalAmount),
+			RouteSourceKey:     attempt.Route.SourcePubKey[:],
+		})
+		if err != nil {
+			return fmt.Errorf("failed to insert HTLC "+
+				"attempt: %w", err)
+		}
+
+		// Insert the route level first hop custom records.
+		attemptFirstHopCustomRecords := attempt.Route.
+			FirstHopWireCustomRecords
+
+		for key, value := range attemptFirstHopCustomRecords {
+			//nolint:ll
+			err = db.InsertPaymentAttemptFirstHopCustomRecord(
+				ctx,
+				sqlc.InsertPaymentAttemptFirstHopCustomRecordParams{
+					HtlcAttemptIndex: int64(attempt.AttemptID),
+					Key:              int64(key),
+					Value:            value,
+				},
+			)
+			if err != nil {
+				return fmt.Errorf("failed to insert "+
+					"payment attempt first hop custom "+
+					"record: %w", err)
+			}
+		}
+
+		// Insert the route hops.
+		err = s.insertRouteHops(
+			ctx, db, attempt.Route.Hops, attempt.AttemptID,
+		)
+		if err != nil {
+			return fmt.Errorf("failed to insert route hops: %w",
+				err)
+		}
+
+		// We fetch the HTLC attempts again to recalculate the payment
+		// state after the attempt is registered. This also makes sure
+		// we have the right data in case multiple attempts are
+		// registered concurrently.
+		//
+		// NOTE: While the caller is responsible for serializing calls
+		// to RegisterAttempt per payment hash (see PaymentControl
+		// interface), we still refetch here to guarantee we return
+		// consistent, up-to-date data that reflects all changes made
+		// within this transaction.
+		mpPayment, err = fetchPaymentWithCompleteData(
+			ctx, s.cfg.QueryCfg, db, dbPayment,
+		)
+		if err != nil {
+			return fmt.Errorf("failed to fetch payment with "+
+				"complete data: %w", err)
+		}
+
+		return nil
+	}, func() {
+		mpPayment = nil
+	})
+	if err != nil {
+		return nil, fmt.Errorf("failed to register attempt: %w", err)
+	}
+
+	return mpPayment, nil
+}
+
+// SettleAttempt marks the specified HTLC attempt as successfully settled,
+// recording the payment preimage and settlement time. The preimage serves as
+// cryptographic proof of payment and is atomically saved to the database.
+//
+// This method is part of the PaymentControl interface, which is embedded in
+// the PaymentWriter interface and ultimately the DB interface. It represents
+// step 3a in the payment lifecycle control flow (step 3b is FailAttempt),
+// called after RegisterAttempt when an HTLC successfully completes.
+func (s *SQLStore) SettleAttempt(ctx context.Context, paymentHash lntypes.Hash,
+	attemptID uint64, settleInfo *HTLCSettleInfo) (*MPPayment, error) {
+
+	var mpPayment *MPPayment
+
+	err := s.db.ExecTx(ctx, sqldb.WriteTxOpt(), func(db SQLQueries) error {
+		dbPayment, err := fetchPaymentByHash(ctx, db, paymentHash)
+		if err != nil {
+			return err
+		}
+
+		paymentStatus, err := computePaymentStatusFromDB(
+			ctx, s.cfg.QueryCfg, db, dbPayment,
+		)
+		if err != nil {
+			return fmt.Errorf("failed to compute payment "+
+				"status: %w", err)
+		}
+
+		if err := paymentStatus.updatable(); err != nil {
+			return fmt.Errorf("payment is not updatable: %w", err)
+		}
+
+		err = db.SettleAttempt(ctx, sqlc.SettleAttemptParams{
+			AttemptIndex:   int64(attemptID),
+			ResolutionTime: time.Now(),
+			ResolutionType: int32(HTLCAttemptResolutionSettled),
+			SettlePreimage: settleInfo.Preimage[:],
+		})
+		if err != nil {
+			return fmt.Errorf("failed to settle attempt: %w", err)
+		}
+
+		// Fetch the complete payment after we settled the attempt.
+		mpPayment, err = fetchPaymentWithCompleteData(
+			ctx, s.cfg.QueryCfg, db, dbPayment,
+		)
+		if err != nil {
+			return fmt.Errorf("failed to fetch payment with "+
+				"complete data: %w", err)
+		}
+
+		return nil
+	}, func() {
+		mpPayment = nil
+	})
+	if err != nil {
+		return nil, fmt.Errorf("failed to settle attempt: %w", err)
+	}
+
+	return mpPayment, nil
+}
+
+// FailAttempt marks the specified HTLC attempt as failed, recording the
+// failure reason, failure time, optional failure message, and the index of the
+// node in the route that generated the failure. This information is atomically
+// saved to the database for debugging and route optimization purposes.
+//
+// For single-path payments, failing the only attempt may lead to the payment
+// being retried or ultimately failed via the Fail method. For multi-shard
+// (MPP/AMP) payments, individual shard failures don't necessarily fail the
+// entire payment; additional attempts can be registered until sufficient shards
+// succeed or the payment is permanently failed.
+//
+// Returns the updated MPPayment with the attempt marked as failed and the
+// payment state recalculated. The payment status remains StatusInFlight if
+// other attempts are still in flight, or may transition based on the overall
+// payment state.
+//
+// This method is part of the PaymentControl interface, which is embedded in
+// the PaymentWriter interface and ultimately the DB interface. It represents
+// step 3b in the payment lifecycle control flow (step 3a is SettleAttempt),
+// called after RegisterAttempt when an HTLC fails.
+func (s *SQLStore) FailAttempt(ctx context.Context, paymentHash lntypes.Hash,
+	attemptID uint64, failInfo *HTLCFailInfo) (*MPPayment, error) {
+
+	var mpPayment *MPPayment
+
+	err := s.db.ExecTx(ctx, sqldb.WriteTxOpt(), func(db SQLQueries) error {
+		// Make sure the payment exists.
+		dbPayment, err := fetchPaymentByHash(ctx, db, paymentHash)
+		if err != nil {
+			return err
+		}
+
+		paymentStatus, err := computePaymentStatusFromDB(
+			ctx, s.cfg.QueryCfg, db, dbPayment,
+		)
+		if err != nil {
+			return fmt.Errorf("failed to compute payment "+
+				"status: %w", err)
+		}
+
+		// We check if the payment is updatable before failing the
+		// attempt.
+		if err := paymentStatus.updatable(); err != nil {
+			return fmt.Errorf("payment is not updatable: %w", err)
+		}
+
+		var failureMsg bytes.Buffer
+		if failInfo.Message != nil {
+			err := lnwire.EncodeFailureMessage(
+				&failureMsg, failInfo.Message, 0,
+			)
+			if err != nil {
+				return fmt.Errorf("failed to encode "+
+					"failure message: %w", err)
+			}
+		}
+
+		err = db.FailAttempt(ctx, sqlc.FailAttemptParams{
+			AttemptIndex:   int64(attemptID),
+			ResolutionTime: time.Now(),
+			ResolutionType: int32(HTLCAttemptResolutionFailed),
+			FailureSourceIndex: sqldb.SQLInt32(
+				failInfo.FailureSourceIndex,
+			),
+			HtlcFailReason: sqldb.SQLInt32(failInfo.Reason),
+			FailureMsg:     failureMsg.Bytes(),
+		})
+		if err != nil {
+			return fmt.Errorf("failed to fail attempt: %w", err)
+		}
+
+		mpPayment, err = fetchPaymentWithCompleteData(
+			ctx, s.cfg.QueryCfg, db, dbPayment,
+		)
+		if err != nil {
+			return fmt.Errorf("failed to fetch payment with "+
+				"complete data: %w", err)
+		}
+
+		return nil
+	}, func() {
+		mpPayment = nil
+	})
+	if err != nil {
+		return nil, fmt.Errorf("failed to fail attempt: %w", err)
+	}
+
+	return mpPayment, nil
+}
+
+// Fail records the ultimate reason why a payment failed. This method stores
+// the failure reason for record keeping but does not enforce that all HTLC
+// attempts are resolved - HTLCs may still be in flight when this is called.
+//
+// The payment's actual status transition to StatusFailed is determined by the
+// payment state calculation, which considers both the recorded failure reason
+// and the current state of all HTLC attempts. The status will transition to
+// StatusFailed once all HTLCs are resolved and/or a failure reason is recorded.
+//
+// NOTE: According to the interface contract, this should only be called when
+// all active attempts are already failed. However, the implementation allows
+// concurrent calls and does not validate this precondition, enabling the last
+// failing attempt to record the failure reason without synchronization.
+//
+// This method is part of the PaymentControl interface, which is embedded in
+// the PaymentWriter interface and ultimately the DB interface. It represents
+// step 4 in the payment lifecycle control flow.
+func (s *SQLStore) Fail(ctx context.Context, paymentHash lntypes.Hash,
+	reason FailureReason) (*MPPayment, error) {
+
+	var mpPayment *MPPayment
+
+	err := s.db.ExecTx(ctx, sqldb.WriteTxOpt(), func(db SQLQueries) error {
+		result, err := db.FailPayment(ctx, sqlc.FailPaymentParams{
+			PaymentIdentifier: paymentHash[:],
+			FailReason:        sqldb.SQLInt32(reason),
+		})
+		if err != nil {
+			return err
+		}
+
+		rowsAffected, err := result.RowsAffected()
+		if err != nil {
+			return err
+		}
+		if rowsAffected == 0 {
+			return ErrPaymentNotInitiated
+		}
+
+		payment, err := db.FetchPayment(ctx, paymentHash[:])
+		if err != nil {
+			return fmt.Errorf("failed to fetch payment: %w", err)
+		}
+		mpPayment, err = fetchPaymentWithCompleteData(
+			ctx, s.cfg.QueryCfg, db, payment,
+		)
+		if err != nil {
+			return fmt.Errorf("failed to fetch payment with "+
+				"complete data: %w", err)
+		}
+
+		return nil
+	}, func() {
+		mpPayment = nil
+	})
+	if err != nil {
+		return nil, fmt.Errorf("failed to fail payment: %w", err)
+	}
+
+	return mpPayment, nil
+}
+
+// DeletePayments performs a batch deletion of payments or their failed HTLC
+// attempts from the database based on the specified flags. This is a bulk
+// operation that iterates through all payments and selectively deletes based
+// on the criteria.
+// The behavior is controlled by two flags:
+//
+// If failedAttemptsOnly is true, only failed HTLC attempts are deleted while
+// preserving the payment records and any successful or in-flight attempts.
+// The return value is always 0 when deleting attempts only.
+//
+// If failedAttemptsOnly is false, entire payment records are deleted including
+// all associated data (HTLCs, metadata, intents). The return value is the
+// number of payments deleted.
+//
+// The failedOnly flag further filters which payments are processed:
+//   - failedOnly=true, failedAttemptsOnly=true: Delete failed attempts for
+//     StatusFailed payments only
+//   - failedOnly=false, failedAttemptsOnly=true: Delete failed attempts for
+//     all removable payments
+//   - failedOnly=true, failedAttemptsOnly=false: Delete entire payment records
+//     for StatusFailed payments only
+//   - failedOnly=false, failedAttemptsOnly=false: Delete all removable payment
+//     records (StatusInitiated, StatusSucceeded, StatusFailed)
+//
+// Safety checks applied to all operations:
+//   - Payments with StatusInFlight are always skipped (cannot be safely deleted
+//     while HTLCs are on the network)
+//   - The payment status must pass the removable() check
+//
+// Returns the number of complete payments deleted (0 if only deleting failed
+// attempts). This is useful for cleanup operations, administrative maintenance,
+// or freeing up database storage.
+//
+// This method is part of the PaymentWriter interface, which is embedded in
+// the DB interface.
+//
+// TODO(ziggie): batch and use iterator instead, moreover we dont need to fetch
+// the complete payment data for each payment, we can just fetch the payment ID
+// and the resolution types to decide if the payment is removable.
+func (s *SQLStore) DeletePayments(ctx context.Context, failedOnly,
+	failedHtlcsOnly bool) (int, error) {
+
+	var numPayments int
+
+	extractCursor := func(row sqlc.FilterPaymentsRow) int64 {
+		return row.Payment.ID
+	}
+
+	err := s.db.ExecTx(ctx, sqldb.WriteTxOpt(), func(db SQLQueries) error {
+		// collectFunc extracts the payment ID from each payment row.
+		collectFunc := func(row sqlc.FilterPaymentsRow) (int64, error) {
+			return row.Payment.ID, nil
+		}
+
+		// batchDataFunc loads only HTLC resolution types for a batch
+		// of payments, which is sufficient to determine payment status.
+		batchDataFunc := func(ctx context.Context, paymentIDs []int64) (
+			*paymentStatusData, error) {
+
+			return batchLoadPaymentResolutions(
+				ctx, s.cfg.QueryCfg, db, paymentIDs,
+			)
+		}
+
+		// processPayment processes each payment with the lightweight
+		// batch-loaded resolution data.
+		processPayment := func(ctx context.Context,
+			dbPayment sqlc.FilterPaymentsRow,
+			batchData *paymentStatusData) error {
+
+			payment := dbPayment.Payment
+
+			// Compute the payment status from resolution types and
+			// failure reason without building the complete payment.
+			resolutionTypes := batchData.resolutionTypes[payment.ID]
+			status, err := computePaymentStatusFromResolutions(
+				resolutionTypes, payment.FailReason,
+			)
+			if err != nil {
+				return fmt.Errorf("failed to compute payment "+
+					"status: %w", err)
+			}
+
+			// Payments which are not final yet cannot be deleted.
+			// we skip them.
+			if err := status.removable(); err != nil {
+				return nil
+			}
+
+			// If we are only deleting failed payments, we skip
+			// if the payment is not failed.
+			if failedOnly && status != StatusFailed {
+				return nil
+			}
+
+			// If we are only deleting failed HTLCs, we delete them
+			// and return early.
+			if failedHtlcsOnly {
+				return db.DeleteFailedAttempts(
+					ctx, payment.ID,
+				)
+			}
+
+			// Otherwise we delete the payment.
+			err = db.DeletePayment(ctx, payment.ID)
+			if err != nil {
+				return fmt.Errorf("failed to delete "+
+					"payment: %w", err)
+			}
+
+			numPayments++
+
+			return nil
+		}
+
+		queryFunc := func(ctx context.Context, lastID int64,
+			limit int32) ([]sqlc.FilterPaymentsRow, error) {
+
+			filterParams := sqlc.FilterPaymentsParams{
+				NumLimit:     limit,
+				CreatedAfter: time.Unix(0, 0).UTC(),
+				CreatedBefore: time.Date(
+					9999, 12, 31, 23, 59, 59,
+					0, time.UTC,
+				),
+				IndexOffsetGet: sqldb.SQLInt64(
+					lastID,
+				),
+			}
+
+			return db.FilterPayments(ctx, filterParams)
+		}
+
+		return sqldb.ExecuteCollectAndBatchWithSharedDataQuery(
+			ctx, s.cfg.QueryCfg, int64(-1), queryFunc,
+			extractCursor, collectFunc, batchDataFunc,
+			processPayment,
+		)
+	}, func() {
+		numPayments = 0
+	})
+	if err != nil {
+		return 0, fmt.Errorf("failed to delete payments "+
+			"(failedOnly: %v, failedHtlcsOnly: %v): %w",
+			failedOnly, failedHtlcsOnly, err)
+	}
+
+	return numPayments, nil
+}
diff --git a/payments/db/migration1/sqlc/db.go b/payments/db/migration1/sqlc/db.go
new file mode 100644
index 0000000..e4d7828
--- /dev/null
+++ b/payments/db/migration1/sqlc/db.go
@@ -0,0 +1,31 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.29.0
+
+package sqlc
+
+import (
+	"context"
+	"database/sql"
+)
+
+type DBTX interface {
+	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
+	PrepareContext(context.Context, string) (*sql.Stmt, error)
+	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
+	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
+}
+
+func New(db DBTX) *Queries {
+	return &Queries{db: db}
+}
+
+type Queries struct {
+	db DBTX
+}
+
+func (q *Queries) WithTx(tx *sql.Tx) *Queries {
+	return &Queries{
+		db: tx,
+	}
+}
diff --git a/payments/db/migration1/sqlc/db_custom.go b/payments/db/migration1/sqlc/db_custom.go
new file mode 100644
index 0000000..625e65a
--- /dev/null
+++ b/payments/db/migration1/sqlc/db_custom.go
@@ -0,0 +1,123 @@
+package sqlc
+
+import (
+	"fmt"
+	"strings"
+)
+
+// GetTx returns the underlying DBTX (either *sql.DB or *sql.Tx) used by the
+// Queries struct.
+func (q *Queries) GetTx() DBTX {
+	return q.db
+}
+
+// makeQueryParams generates a string of query parameters for a SQL query. It is
+// meant to replace the `?` placeholders in a SQL query with numbered parameters
+// like `$1`, `$2`, etc. This is required for the sqlc /*SLICE:<field_name>*/
+// workaround. See scripts/gen_sqlc_docker.sh for more details.
+func makeQueryParams(numTotalArgs, numListArgs int) string {
+	if numListArgs == 0 {
+		return ""
+	}
+
+	var b strings.Builder
+
+	// Pre-allocate a rough estimation of the buffer size to avoid
+	// re-allocations. A parameter like $1000, takes 6 bytes.
+	b.Grow(numListArgs * 6)
+
+	diff := numTotalArgs - numListArgs
+	for i := 0; i < numListArgs; i++ {
+		if i > 0 {
+			// We don't need to check the error here because the
+			// WriteString method of strings.Builder always returns
+			// nil.
+			_, _ = b.WriteString(",")
+		}
+
+		// We don't need to check the error here because the
+		// Write method (called by fmt.Fprintf) of strings.Builder
+		// always returns nil.
+		_, _ = fmt.Fprintf(&b, "$%d", i+diff+1)
+	}
+
+	return b.String()
+}
+
+// PaymentAndIntent is an interface that provides access to a payment and its
+// associated payment intent.
+type PaymentAndIntent interface {
+	// GetPayment returns the Payment associated with this interface.
+	GetPayment() Payment
+
+	// GetPaymentIntent returns the PaymentIntent associated with this
+	// payment.
+	GetPaymentIntent() PaymentIntent
+}
+
+// GetPayment returns the Payment associated with this interface.
+//
+// NOTE: This method is part of the PaymentAndIntent interface.
+func (r FilterPaymentsRow) GetPayment() Payment {
+	return r.Payment
+}
+
+// GetPaymentIntent returns the PaymentIntent associated with this payment.
+// If the payment has no intent (IntentType is NULL), this returns a zero-value
+// PaymentIntent.
+//
+// NOTE: This method is part of the PaymentAndIntent interface.
+func (r FilterPaymentsRow) GetPaymentIntent() PaymentIntent {
+	if !r.IntentType.Valid {
+		return PaymentIntent{}
+	}
+
+	return PaymentIntent{
+		IntentType:    r.IntentType.Int16,
+		IntentPayload: r.IntentPayload,
+	}
+}
+
+// GetPayment returns the Payment associated with this interface.
+//
+// NOTE: This method is part of the PaymentAndIntent interface.
+func (r FetchPaymentRow) GetPayment() Payment {
+	return r.Payment
+}
+
+// GetPaymentIntent returns the PaymentIntent associated with this payment.
+// If the payment has no intent (IntentType is NULL), this returns a zero-value
+// PaymentIntent.
+//
+// NOTE: This method is part of the PaymentAndIntent interface.
+func (r FetchPaymentRow) GetPaymentIntent() PaymentIntent {
+	if !r.IntentType.Valid {
+		return PaymentIntent{}
+	}
+
+	return PaymentIntent{
+		IntentType:    r.IntentType.Int16,
+		IntentPayload: r.IntentPayload,
+	}
+}
+
+func (r FetchPaymentsByIDsRow) GetPayment() Payment {
+	return Payment{
+		ID:                r.ID,
+		AmountMsat:        r.AmountMsat,
+		CreatedAt:         r.CreatedAt,
+		PaymentIdentifier: r.PaymentIdentifier,
+		FailReason:        r.FailReason,
+	}
+}
+
+func (r FetchPaymentsByIDsRow) GetPaymentIntent() PaymentIntent {
+	if !r.IntentType.Valid {
+		return PaymentIntent{}
+	}
+
+	return PaymentIntent{
+		IntentType:    r.IntentType.Int16,
+		IntentPayload: r.IntentPayload,
+	}
+}
diff --git a/payments/db/migration1/sqlc/models.go b/payments/db/migration1/sqlc/models.go
new file mode 100644
index 0000000..afb4480
--- /dev/null
+++ b/payments/db/migration1/sqlc/models.go
@@ -0,0 +1,110 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.29.0
+
+package sqlc
+
+import (
+	"database/sql"
+	"time"
+)
+
+type Payment struct {
+	ID                int64
+	AmountMsat        int64
+	CreatedAt         time.Time
+	PaymentIdentifier []byte
+	FailReason        sql.NullInt32
+}
+
+type PaymentAttemptFirstHopCustomRecord struct {
+	ID               int64
+	HtlcAttemptIndex int64
+	Key              int64
+	Value            []byte
+}
+
+type PaymentDuplicate struct {
+	ID             int64
+	PaymentID      int64
+	AmountMsat     int64
+	CreatedAt      time.Time
+	FailReason     sql.NullInt32
+	SettlePreimage []byte
+	SettleTime     sql.NullTime
+}
+
+type PaymentFirstHopCustomRecord struct {
+	ID        int64
+	PaymentID int64
+	Key       int64
+	Value     []byte
+}
+
+type PaymentHopCustomRecord struct {
+	ID    int64
+	HopID int64
+	Key   int64
+	Value []byte
+}
+
+type PaymentHtlcAttempt struct {
+	ID                 int64
+	AttemptIndex       int64
+	PaymentID          int64
+	SessionKey         []byte
+	AttemptTime        time.Time
+	PaymentHash        []byte
+	FirstHopAmountMsat int64
+	RouteTotalTimeLock int32
+	RouteTotalAmount   int64
+	RouteSourceKey     []byte
+}
+
+type PaymentHtlcAttemptResolution struct {
+	AttemptIndex       int64
+	ResolutionTime     time.Time
+	ResolutionType     int32
+	SettlePreimage     []byte
+	FailureSourceIndex sql.NullInt32
+	HtlcFailReason     sql.NullInt32
+	FailureMsg         []byte
+}
+
+type PaymentIntent struct {
+	ID            int64
+	PaymentID     int64
+	IntentType    int16
+	IntentPayload []byte
+}
+
+type PaymentRouteHop struct {
+	ID               int64
+	HtlcAttemptIndex int64
+	HopIndex         int32
+	PubKey           []byte
+	Scid             string
+	OutgoingTimeLock int32
+	AmtToForward     int64
+	MetaData         []byte
+}
+
+type PaymentRouteHopAmp struct {
+	HopID      int64
+	RootShare  []byte
+	SetID      []byte
+	ChildIndex int32
+}
+
+type PaymentRouteHopBlinded struct {
+	HopID               int64
+	EncryptedData       []byte
+	BlindingPoint       []byte
+	BlindedPathTotalAmt sql.NullInt64
+}
+
+type PaymentRouteHopMpp struct {
+	HopID       int64
+	PaymentAddr []byte
+	TotalMsat   int64
+}
diff --git a/payments/db/migration1/sqlc/payments.sql.go b/payments/db/migration1/sqlc/payments.sql.go
new file mode 100644
index 0000000..e17bb3c
--- /dev/null
+++ b/payments/db/migration1/sqlc/payments.sql.go
@@ -0,0 +1,1357 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.29.0
+// source: payments.sql
+
+package sqlc
+
+import (
+	"context"
+	"database/sql"
+	"strings"
+	"time"
+)
+
+const countPayments = `-- name: CountPayments :one
+SELECT COUNT(*) FROM payments
+`
+
+func (q *Queries) CountPayments(ctx context.Context) (int64, error) {
+	row := q.db.QueryRowContext(ctx, countPayments)
+	var count int64
+	err := row.Scan(&count)
+	return count, err
+}
+
+const deleteFailedAttempts = `-- name: DeleteFailedAttempts :exec
+DELETE FROM payment_htlc_attempts WHERE payment_id = $1 AND attempt_index IN (
+    SELECT attempt_index FROM payment_htlc_attempt_resolutions WHERE resolution_type = 2
+)
+`
+
+// Delete all failed HTLC attempts for the given payment. Resolution type 2
+// indicates a failed attempt.
+func (q *Queries) DeleteFailedAttempts(ctx context.Context, paymentID int64) error {
+	_, err := q.db.ExecContext(ctx, deleteFailedAttempts, paymentID)
+	return err
+}
+
+const deletePayment = `-- name: DeletePayment :exec
+DELETE FROM payments WHERE id = $1
+`
+
+func (q *Queries) DeletePayment(ctx context.Context, id int64) error {
+	_, err := q.db.ExecContext(ctx, deletePayment, id)
+	return err
+}
+
+const failAttempt = `-- name: FailAttempt :exec
+INSERT INTO payment_htlc_attempt_resolutions (
+    attempt_index,
+    resolution_time,
+    resolution_type,
+    failure_source_index,
+    htlc_fail_reason,
+    failure_msg
+)
+VALUES (
+    $1,
+    $2,
+    $3,
+    $4,
+    $5,
+    $6
+)
+`
+
+type FailAttemptParams struct {
+	AttemptIndex       int64
+	ResolutionTime     time.Time
+	ResolutionType     int32
+	FailureSourceIndex sql.NullInt32
+	HtlcFailReason     sql.NullInt32
+	FailureMsg         []byte
+}
+
+func (q *Queries) FailAttempt(ctx context.Context, arg FailAttemptParams) error {
+	_, err := q.db.ExecContext(ctx, failAttempt,
+		arg.AttemptIndex,
+		arg.ResolutionTime,
+		arg.ResolutionType,
+		arg.FailureSourceIndex,
+		arg.HtlcFailReason,
+		arg.FailureMsg,
+	)
+	return err
+}
+
+const failPayment = `-- name: FailPayment :execresult
+UPDATE payments SET fail_reason = $1 WHERE payment_identifier = $2
+`
+
+type FailPaymentParams struct {
+	FailReason        sql.NullInt32
+	PaymentIdentifier []byte
+}
+
+func (q *Queries) FailPayment(ctx context.Context, arg FailPaymentParams) (sql.Result, error) {
+	return q.db.ExecContext(ctx, failPayment, arg.FailReason, arg.PaymentIdentifier)
+}
+
+const fetchAllInflightAttempts = `-- name: FetchAllInflightAttempts :many
+SELECT
+    ha.id,
+    ha.attempt_index,
+    ha.payment_id,
+    ha.session_key,
+    ha.attempt_time,
+    ha.payment_hash,
+    ha.first_hop_amount_msat,
+    ha.route_total_time_lock,
+    ha.route_total_amount,
+    ha.route_source_key
+FROM payment_htlc_attempts ha
+WHERE NOT EXISTS (
+    SELECT 1 FROM payment_htlc_attempt_resolutions hr
+    WHERE hr.attempt_index = ha.attempt_index
+)
+AND ha.attempt_index > $1
+ORDER BY ha.attempt_index ASC
+LIMIT $2
+`
+
+type FetchAllInflightAttemptsParams struct {
+	AttemptIndex int64
+	Limit        int32
+}
+
+// Fetch all inflight attempts with their payment data using pagination.
+// Returns attempt data joined with payment and intent data to avoid separate queries.
+func (q *Queries) FetchAllInflightAttempts(ctx context.Context, arg FetchAllInflightAttemptsParams) ([]PaymentHtlcAttempt, error) {
+	rows, err := q.db.QueryContext(ctx, fetchAllInflightAttempts, arg.AttemptIndex, arg.Limit)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []PaymentHtlcAttempt
+	for rows.Next() {
+		var i PaymentHtlcAttempt
+		if err := rows.Scan(
+			&i.ID,
+			&i.AttemptIndex,
+			&i.PaymentID,
+			&i.SessionKey,
+			&i.AttemptTime,
+			&i.PaymentHash,
+			&i.FirstHopAmountMsat,
+			&i.RouteTotalTimeLock,
+			&i.RouteTotalAmount,
+			&i.RouteSourceKey,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const fetchHopLevelCustomRecords = `-- name: FetchHopLevelCustomRecords :many
+SELECT
+    l.id,
+    l.hop_id,
+    l.key,
+    l.value
+FROM payment_hop_custom_records l
+WHERE l.hop_id IN (/*SLICE:hop_ids*/?)
+ORDER BY l.hop_id ASC, l.key ASC
+`
+
+func (q *Queries) FetchHopLevelCustomRecords(ctx context.Context, hopIds []int64) ([]PaymentHopCustomRecord, error) {
+	query := fetchHopLevelCustomRecords
+	var queryParams []interface{}
+	if len(hopIds) > 0 {
+		for _, v := range hopIds {
+			queryParams = append(queryParams, v)
+		}
+		query = strings.Replace(query, "/*SLICE:hop_ids*/?", makeQueryParams(len(queryParams), len(hopIds)), 1)
+	} else {
+		query = strings.Replace(query, "/*SLICE:hop_ids*/?", "NULL", 1)
+	}
+	rows, err := q.db.QueryContext(ctx, query, queryParams...)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []PaymentHopCustomRecord
+	for rows.Next() {
+		var i PaymentHopCustomRecord
+		if err := rows.Scan(
+			&i.ID,
+			&i.HopID,
+			&i.Key,
+			&i.Value,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const fetchHopsForAttempts = `-- name: FetchHopsForAttempts :many
+SELECT
+    h.id,
+    h.htlc_attempt_index,
+    h.hop_index,
+    h.pub_key,
+    h.scid,
+    h.outgoing_time_lock,
+    h.amt_to_forward,
+    h.meta_data,
+    m.payment_addr AS mpp_payment_addr,
+    m.total_msat AS mpp_total_msat,
+    a.root_share AS amp_root_share,
+    a.set_id AS amp_set_id,
+    a.child_index AS amp_child_index,
+    b.encrypted_data,
+    b.blinding_point,
+    b.blinded_path_total_amt
+FROM payment_route_hops h
+LEFT JOIN payment_route_hop_mpp m ON m.hop_id = h.id
+LEFT JOIN payment_route_hop_amp a ON a.hop_id = h.id
+LEFT JOIN payment_route_hop_blinded b ON b.hop_id = h.id
+WHERE h.htlc_attempt_index IN (/*SLICE:htlc_attempt_indices*/?)
+ORDER BY h.htlc_attempt_index ASC, h.hop_index ASC
+`
+
+type FetchHopsForAttemptsRow struct {
+	ID                  int64
+	HtlcAttemptIndex    int64
+	HopIndex            int32
+	PubKey              []byte
+	Scid                string
+	OutgoingTimeLock    int32
+	AmtToForward        int64
+	MetaData            []byte
+	MppPaymentAddr      []byte
+	MppTotalMsat        sql.NullInt64
+	AmpRootShare        []byte
+	AmpSetID            []byte
+	AmpChildIndex       sql.NullInt32
+	EncryptedData       []byte
+	BlindingPoint       []byte
+	BlindedPathTotalAmt sql.NullInt64
+}
+
+func (q *Queries) FetchHopsForAttempts(ctx context.Context, htlcAttemptIndices []int64) ([]FetchHopsForAttemptsRow, error) {
+	query := fetchHopsForAttempts
+	var queryParams []interface{}
+	if len(htlcAttemptIndices) > 0 {
+		for _, v := range htlcAttemptIndices {
+			queryParams = append(queryParams, v)
+		}
+		query = strings.Replace(query, "/*SLICE:htlc_attempt_indices*/?", makeQueryParams(len(queryParams), len(htlcAttemptIndices)), 1)
+	} else {
+		query = strings.Replace(query, "/*SLICE:htlc_attempt_indices*/?", "NULL", 1)
+	}
+	rows, err := q.db.QueryContext(ctx, query, queryParams...)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []FetchHopsForAttemptsRow
+	for rows.Next() {
+		var i FetchHopsForAttemptsRow
+		if err := rows.Scan(
+			&i.ID,
+			&i.HtlcAttemptIndex,
+			&i.HopIndex,
+			&i.PubKey,
+			&i.Scid,
+			&i.OutgoingTimeLock,
+			&i.AmtToForward,
+			&i.MetaData,
+			&i.MppPaymentAddr,
+			&i.MppTotalMsat,
+			&i.AmpRootShare,
+			&i.AmpSetID,
+			&i.AmpChildIndex,
+			&i.EncryptedData,
+			&i.BlindingPoint,
+			&i.BlindedPathTotalAmt,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const fetchHtlcAttemptResolutionsForPayments = `-- name: FetchHtlcAttemptResolutionsForPayments :many
+SELECT
+    ha.payment_id,
+    hr.resolution_type
+FROM payment_htlc_attempts ha
+LEFT JOIN payment_htlc_attempt_resolutions hr ON hr.attempt_index = ha.attempt_index
+WHERE ha.payment_id IN (/*SLICE:payment_ids*/?)
+`
+
+type FetchHtlcAttemptResolutionsForPaymentsRow struct {
+	PaymentID      int64
+	ResolutionType sql.NullInt32
+}
+
+// Batch query to fetch only HTLC resolution status for multiple payments.
+// We don't need to order by payment_id and attempt_time because we will
+// group the resolutions by payment_id in the background.
+func (q *Queries) FetchHtlcAttemptResolutionsForPayments(ctx context.Context, paymentIds []int64) ([]FetchHtlcAttemptResolutionsForPaymentsRow, error) {
+	query := fetchHtlcAttemptResolutionsForPayments
+	var queryParams []interface{}
+	if len(paymentIds) > 0 {
+		for _, v := range paymentIds {
+			queryParams = append(queryParams, v)
+		}
+		query = strings.Replace(query, "/*SLICE:payment_ids*/?", makeQueryParams(len(queryParams), len(paymentIds)), 1)
+	} else {
+		query = strings.Replace(query, "/*SLICE:payment_ids*/?", "NULL", 1)
+	}
+	rows, err := q.db.QueryContext(ctx, query, queryParams...)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []FetchHtlcAttemptResolutionsForPaymentsRow
+	for rows.Next() {
+		var i FetchHtlcAttemptResolutionsForPaymentsRow
+		if err := rows.Scan(&i.PaymentID, &i.ResolutionType); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const fetchHtlcAttemptsForPayments = `-- name: FetchHtlcAttemptsForPayments :many
+SELECT
+    ha.id,
+    ha.attempt_index,
+    ha.payment_id,
+    ha.session_key,
+    ha.attempt_time,
+    ha.payment_hash,
+    ha.first_hop_amount_msat,
+    ha.route_total_time_lock,
+    ha.route_total_amount,
+    ha.route_source_key,
+    hr.resolution_type,
+    hr.resolution_time,
+    hr.failure_source_index,
+    hr.htlc_fail_reason,
+    hr.failure_msg,
+    hr.settle_preimage
+FROM payment_htlc_attempts ha
+LEFT JOIN payment_htlc_attempt_resolutions hr ON hr.attempt_index = ha.attempt_index
+WHERE ha.payment_id IN (/*SLICE:payment_ids*/?)
+ORDER BY ha.payment_id ASC, ha.attempt_time ASC
+`
+
+type FetchHtlcAttemptsForPaymentsRow struct {
+	ID                 int64
+	AttemptIndex       int64
+	PaymentID          int64
+	SessionKey         []byte
+	AttemptTime        time.Time
+	PaymentHash        []byte
+	FirstHopAmountMsat int64
+	RouteTotalTimeLock int32
+	RouteTotalAmount   int64
+	RouteSourceKey     []byte
+	ResolutionType     sql.NullInt32
+	ResolutionTime     sql.NullTime
+	FailureSourceIndex sql.NullInt32
+	HtlcFailReason     sql.NullInt32
+	FailureMsg         []byte
+	SettlePreimage     []byte
+}
+
+func (q *Queries) FetchHtlcAttemptsForPayments(ctx context.Context, paymentIds []int64) ([]FetchHtlcAttemptsForPaymentsRow, error) {
+	query := fetchHtlcAttemptsForPayments
+	var queryParams []interface{}
+	if len(paymentIds) > 0 {
+		for _, v := range paymentIds {
+			queryParams = append(queryParams, v)
+		}
+		query = strings.Replace(query, "/*SLICE:payment_ids*/?", makeQueryParams(len(queryParams), len(paymentIds)), 1)
+	} else {
+		query = strings.Replace(query, "/*SLICE:payment_ids*/?", "NULL", 1)
+	}
+	rows, err := q.db.QueryContext(ctx, query, queryParams...)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []FetchHtlcAttemptsForPaymentsRow
+	for rows.Next() {
+		var i FetchHtlcAttemptsForPaymentsRow
+		if err := rows.Scan(
+			&i.ID,
+			&i.AttemptIndex,
+			&i.PaymentID,
+			&i.SessionKey,
+			&i.AttemptTime,
+			&i.PaymentHash,
+			&i.FirstHopAmountMsat,
+			&i.RouteTotalTimeLock,
+			&i.RouteTotalAmount,
+			&i.RouteSourceKey,
+			&i.ResolutionType,
+			&i.ResolutionTime,
+			&i.FailureSourceIndex,
+			&i.HtlcFailReason,
+			&i.FailureMsg,
+			&i.SettlePreimage,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const fetchPayment = `-- name: FetchPayment :one
+SELECT
+    p.id, p.amount_msat, p.created_at, p.payment_identifier, p.fail_reason,
+    i.intent_type AS "intent_type",
+    i.intent_payload AS "intent_payload"
+FROM payments p
+LEFT JOIN payment_intents i ON i.payment_id = p.id
+WHERE p.payment_identifier = $1
+`
+
+type FetchPaymentRow struct {
+	Payment       Payment
+	IntentType    sql.NullInt16
+	IntentPayload []byte
+}
+
+func (q *Queries) FetchPayment(ctx context.Context, paymentIdentifier []byte) (FetchPaymentRow, error) {
+	row := q.db.QueryRowContext(ctx, fetchPayment, paymentIdentifier)
+	var i FetchPaymentRow
+	err := row.Scan(
+		&i.Payment.ID,
+		&i.Payment.AmountMsat,
+		&i.Payment.CreatedAt,
+		&i.Payment.PaymentIdentifier,
+		&i.Payment.FailReason,
+		&i.IntentType,
+		&i.IntentPayload,
+	)
+	return i, err
+}
+
+const fetchPaymentDuplicates = `-- name: FetchPaymentDuplicates :many
+SELECT
+    id,
+    payment_id,
+    amount_msat,
+    created_at,
+    fail_reason,
+    settle_preimage,
+    settle_time
+FROM payment_duplicates
+WHERE payment_id = $1
+ORDER BY id ASC
+`
+
+// Fetch all duplicate payment records from the payment_duplicates table for
+// a given payment ID.
+func (q *Queries) FetchPaymentDuplicates(ctx context.Context, paymentID int64) ([]PaymentDuplicate, error) {
+	rows, err := q.db.QueryContext(ctx, fetchPaymentDuplicates, paymentID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []PaymentDuplicate
+	for rows.Next() {
+		var i PaymentDuplicate
+		if err := rows.Scan(
+			&i.ID,
+			&i.PaymentID,
+			&i.AmountMsat,
+			&i.CreatedAt,
+			&i.FailReason,
+			&i.SettlePreimage,
+			&i.SettleTime,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const fetchPaymentLevelFirstHopCustomRecords = `-- name: FetchPaymentLevelFirstHopCustomRecords :many
+SELECT
+    l.id,
+    l.payment_id,
+    l.key,
+    l.value
+FROM payment_first_hop_custom_records l
+WHERE l.payment_id IN (/*SLICE:payment_ids*/?)
+ORDER BY l.payment_id ASC, l.key ASC
+`
+
+func (q *Queries) FetchPaymentLevelFirstHopCustomRecords(ctx context.Context, paymentIds []int64) ([]PaymentFirstHopCustomRecord, error) {
+	query := fetchPaymentLevelFirstHopCustomRecords
+	var queryParams []interface{}
+	if len(paymentIds) > 0 {
+		for _, v := range paymentIds {
+			queryParams = append(queryParams, v)
+		}
+		query = strings.Replace(query, "/*SLICE:payment_ids*/?", makeQueryParams(len(queryParams), len(paymentIds)), 1)
+	} else {
+		query = strings.Replace(query, "/*SLICE:payment_ids*/?", "NULL", 1)
+	}
+	rows, err := q.db.QueryContext(ctx, query, queryParams...)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []PaymentFirstHopCustomRecord
+	for rows.Next() {
+		var i PaymentFirstHopCustomRecord
+		if err := rows.Scan(
+			&i.ID,
+			&i.PaymentID,
+			&i.Key,
+			&i.Value,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const fetchPaymentsByIDs = `-- name: FetchPaymentsByIDs :many
+SELECT
+    p.id,
+    p.amount_msat,
+    p.created_at,
+    p.payment_identifier,
+    p.fail_reason,
+    pi.intent_type,
+    pi.intent_payload
+FROM payments p
+LEFT JOIN payment_intents pi ON pi.payment_id = p.id
+WHERE p.id IN (/*SLICE:payment_ids*/?)
+ORDER BY p.id ASC
+`
+
+type FetchPaymentsByIDsRow struct {
+	ID                int64
+	AmountMsat        int64
+	CreatedAt         time.Time
+	PaymentIdentifier []byte
+	FailReason        sql.NullInt32
+	IntentType        sql.NullInt16
+	IntentPayload     []byte
+}
+
+// Batch fetch payment and intent data for a set of payment IDs.
+// Used to avoid fetching redundant payment data when processing multiple
+// attempts for the same payment.
+func (q *Queries) FetchPaymentsByIDs(ctx context.Context, paymentIds []int64) ([]FetchPaymentsByIDsRow, error) {
+	query := fetchPaymentsByIDs
+	var queryParams []interface{}
+	if len(paymentIds) > 0 {
+		for _, v := range paymentIds {
+			queryParams = append(queryParams, v)
+		}
+		query = strings.Replace(query, "/*SLICE:payment_ids*/?", makeQueryParams(len(queryParams), len(paymentIds)), 1)
+	} else {
+		query = strings.Replace(query, "/*SLICE:payment_ids*/?", "NULL", 1)
+	}
+	rows, err := q.db.QueryContext(ctx, query, queryParams...)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []FetchPaymentsByIDsRow
+	for rows.Next() {
+		var i FetchPaymentsByIDsRow
+		if err := rows.Scan(
+			&i.ID,
+			&i.AmountMsat,
+			&i.CreatedAt,
+			&i.PaymentIdentifier,
+			&i.FailReason,
+			&i.IntentType,
+			&i.IntentPayload,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const fetchPaymentsByIDsMig = `-- name: FetchPaymentsByIDsMig :many
+SELECT
+    p.id,
+    p.amount_msat,
+    p.created_at,
+    p.payment_identifier,
+    p.fail_reason,
+    COUNT(ha.id) AS htlc_attempt_count
+FROM payments p
+LEFT JOIN payment_htlc_attempts ha ON ha.payment_id = p.id
+WHERE p.id IN (/*SLICE:payment_ids*/?)
+GROUP BY p.id, p.amount_msat, p.created_at, p.payment_identifier, p.fail_reason
+ORDER BY p.id ASC
+`
+
+type FetchPaymentsByIDsMigRow struct {
+	ID                int64
+	AmountMsat        int64
+	CreatedAt         time.Time
+	PaymentIdentifier []byte
+	FailReason        sql.NullInt32
+	HtlcAttemptCount  int64
+}
+
+// Migration-specific batch fetch that returns payment data along with HTLC
+// attempt counts for structural validation during KV to SQL migration.
+func (q *Queries) FetchPaymentsByIDsMig(ctx context.Context, paymentIds []int64) ([]FetchPaymentsByIDsMigRow, error) {
+	query := fetchPaymentsByIDsMig
+	var queryParams []interface{}
+	if len(paymentIds) > 0 {
+		for _, v := range paymentIds {
+			queryParams = append(queryParams, v)
+		}
+		query = strings.Replace(query, "/*SLICE:payment_ids*/?", makeQueryParams(len(queryParams), len(paymentIds)), 1)
+	} else {
+		query = strings.Replace(query, "/*SLICE:payment_ids*/?", "NULL", 1)
+	}
+	rows, err := q.db.QueryContext(ctx, query, queryParams...)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []FetchPaymentsByIDsMigRow
+	for rows.Next() {
+		var i FetchPaymentsByIDsMigRow
+		if err := rows.Scan(
+			&i.ID,
+			&i.AmountMsat,
+			&i.CreatedAt,
+			&i.PaymentIdentifier,
+			&i.FailReason,
+			&i.HtlcAttemptCount,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const fetchRouteLevelFirstHopCustomRecords = `-- name: FetchRouteLevelFirstHopCustomRecords :many
+SELECT
+    l.id,
+    l.htlc_attempt_index,
+    l.key,
+    l.value
+FROM payment_attempt_first_hop_custom_records l
+WHERE l.htlc_attempt_index IN (/*SLICE:htlc_attempt_indices*/?)
+ORDER BY l.htlc_attempt_index ASC, l.key ASC
+`
+
+func (q *Queries) FetchRouteLevelFirstHopCustomRecords(ctx context.Context, htlcAttemptIndices []int64) ([]PaymentAttemptFirstHopCustomRecord, error) {
+	query := fetchRouteLevelFirstHopCustomRecords
+	var queryParams []interface{}
+	if len(htlcAttemptIndices) > 0 {
+		for _, v := range htlcAttemptIndices {
+			queryParams = append(queryParams, v)
+		}
+		query = strings.Replace(query, "/*SLICE:htlc_attempt_indices*/?", makeQueryParams(len(queryParams), len(htlcAttemptIndices)), 1)
+	} else {
+		query = strings.Replace(query, "/*SLICE:htlc_attempt_indices*/?", "NULL", 1)
+	}
+	rows, err := q.db.QueryContext(ctx, query, queryParams...)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []PaymentAttemptFirstHopCustomRecord
+	for rows.Next() {
+		var i PaymentAttemptFirstHopCustomRecord
+		if err := rows.Scan(
+			&i.ID,
+			&i.HtlcAttemptIndex,
+			&i.Key,
+			&i.Value,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const filterPayments = `-- name: FilterPayments :many
+/* ─────────────────────────────────────────────
+   fetch queries
+   ─────────────────────────────────────────────
+*/
+
+SELECT
+    p.id, p.amount_msat, p.created_at, p.payment_identifier, p.fail_reason,
+    i.intent_type AS "intent_type",
+    i.intent_payload AS "intent_payload"
+FROM payments p
+LEFT JOIN payment_intents i ON i.payment_id = p.id
+WHERE p.id > COALESCE($1, -1)
+  AND p.id < COALESCE($2, 9223372036854775807)
+  -- NOTE: We use non-nullable time params with Go-side defaults instead of
+  -- COALESCE, because COALESCE with text fallback causes type mismatch on
+  -- Postgres (timestamp vs text), and OR-based optional filters can prevent
+  -- the planner from using the created_at index.
+  AND p.created_at >= $3
+  AND p.created_at <= $4
+  AND (
+      i.intent_type = $5 OR
+      $5 IS NULL OR i.intent_type IS NULL
+  )
+ORDER BY p.id ASC
+LIMIT $6
+`
+
+type FilterPaymentsParams struct {
+	IndexOffsetGet sql.NullInt64
+	IndexOffsetLet sql.NullInt64
+	CreatedAfter   time.Time
+	CreatedBefore  time.Time
+	IntentType     sql.NullInt16
+	NumLimit       int32
+}
+
+type FilterPaymentsRow struct {
+	Payment       Payment
+	IntentType    sql.NullInt16
+	IntentPayload []byte
+}
+
+func (q *Queries) FilterPayments(ctx context.Context, arg FilterPaymentsParams) ([]FilterPaymentsRow, error) {
+	rows, err := q.db.QueryContext(ctx, filterPayments,
+		arg.IndexOffsetGet,
+		arg.IndexOffsetLet,
+		arg.CreatedAfter,
+		arg.CreatedBefore,
+		arg.IntentType,
+		arg.NumLimit,
+	)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []FilterPaymentsRow
+	for rows.Next() {
+		var i FilterPaymentsRow
+		if err := rows.Scan(
+			&i.Payment.ID,
+			&i.Payment.AmountMsat,
+			&i.Payment.CreatedAt,
+			&i.Payment.PaymentIdentifier,
+			&i.Payment.FailReason,
+			&i.IntentType,
+			&i.IntentPayload,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const filterPaymentsDesc = `-- name: FilterPaymentsDesc :many
+SELECT
+    p.id, p.amount_msat, p.created_at, p.payment_identifier, p.fail_reason,
+    i.intent_type AS "intent_type",
+    i.intent_payload AS "intent_payload"
+FROM payments p
+LEFT JOIN payment_intents i ON i.payment_id = p.id
+WHERE p.id > COALESCE($1, -1)
+  AND p.id < COALESCE($2, 9223372036854775807)
+  -- NOTE: We use non-nullable time params with Go-side defaults instead of
+  -- COALESCE, because COALESCE with text fallback causes type mismatch on
+  -- Postgres (timestamp vs text), and OR-based optional filters can prevent
+  -- the planner from using the created_at index.
+  AND p.created_at >= $3
+  AND p.created_at <= $4
+  AND (
+      i.intent_type = $5 OR
+      $5 IS NULL OR i.intent_type IS NULL
+  )
+ORDER BY p.id DESC
+LIMIT $6
+`
+
+type FilterPaymentsDescParams struct {
+	IndexOffsetGet sql.NullInt64
+	IndexOffsetLet sql.NullInt64
+	CreatedAfter   time.Time
+	CreatedBefore  time.Time
+	IntentType     sql.NullInt16
+	NumLimit       int32
+}
+
+type FilterPaymentsDescRow struct {
+	Payment       Payment
+	IntentType    sql.NullInt16
+	IntentPayload []byte
+}
+
+func (q *Queries) FilterPaymentsDesc(ctx context.Context, arg FilterPaymentsDescParams) ([]FilterPaymentsDescRow, error) {
+	rows, err := q.db.QueryContext(ctx, filterPaymentsDesc,
+		arg.IndexOffsetGet,
+		arg.IndexOffsetLet,
+		arg.CreatedAfter,
+		arg.CreatedBefore,
+		arg.IntentType,
+		arg.NumLimit,
+	)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []FilterPaymentsDescRow
+	for rows.Next() {
+		var i FilterPaymentsDescRow
+		if err := rows.Scan(
+			&i.Payment.ID,
+			&i.Payment.AmountMsat,
+			&i.Payment.CreatedAt,
+			&i.Payment.PaymentIdentifier,
+			&i.Payment.FailReason,
+			&i.IntentType,
+			&i.IntentPayload,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const insertHtlcAttempt = `-- name: InsertHtlcAttempt :one
+INSERT INTO payment_htlc_attempts (
+    payment_id,
+    attempt_index,
+    session_key,
+    attempt_time,
+    payment_hash,
+    first_hop_amount_msat,
+    route_total_time_lock,
+    route_total_amount,
+    route_source_key)
+VALUES (
+    $1,
+    $2,
+    $3,
+    $4,
+    $5, 
+    $6, 
+    $7, 
+    $8, 
+    $9)
+RETURNING id
+`
+
+type InsertHtlcAttemptParams struct {
+	PaymentID          int64
+	AttemptIndex       int64
+	SessionKey         []byte
+	AttemptTime        time.Time
+	PaymentHash        []byte
+	FirstHopAmountMsat int64
+	RouteTotalTimeLock int32
+	RouteTotalAmount   int64
+	RouteSourceKey     []byte
+}
+
+func (q *Queries) InsertHtlcAttempt(ctx context.Context, arg InsertHtlcAttemptParams) (int64, error) {
+	row := q.db.QueryRowContext(ctx, insertHtlcAttempt,
+		arg.PaymentID,
+		arg.AttemptIndex,
+		arg.SessionKey,
+		arg.AttemptTime,
+		arg.PaymentHash,
+		arg.FirstHopAmountMsat,
+		arg.RouteTotalTimeLock,
+		arg.RouteTotalAmount,
+		arg.RouteSourceKey,
+	)
+	var id int64
+	err := row.Scan(&id)
+	return id, err
+}
+
+const insertPayment = `-- name: InsertPayment :one
+INSERT INTO payments (
+    amount_msat, 
+    created_at, 
+    payment_identifier,
+    fail_reason)
+VALUES (
+    $1,
+    $2,
+    $3,
+    NULL
+)
+RETURNING id
+`
+
+type InsertPaymentParams struct {
+	AmountMsat        int64
+	CreatedAt         time.Time
+	PaymentIdentifier []byte
+}
+
+// Insert a new payment and return its ID.
+// When creating a payment we don't have a fail reason because we start the
+// payment process.
+func (q *Queries) InsertPayment(ctx context.Context, arg InsertPaymentParams) (int64, error) {
+	row := q.db.QueryRowContext(ctx, insertPayment, arg.AmountMsat, arg.CreatedAt, arg.PaymentIdentifier)
+	var id int64
+	err := row.Scan(&id)
+	return id, err
+}
+
+const insertPaymentAttemptFirstHopCustomRecord = `-- name: InsertPaymentAttemptFirstHopCustomRecord :exec
+INSERT INTO payment_attempt_first_hop_custom_records (
+    htlc_attempt_index,
+    key,
+    value
+)
+VALUES (
+    $1,
+    $2,
+    $3
+)
+`
+
+type InsertPaymentAttemptFirstHopCustomRecordParams struct {
+	HtlcAttemptIndex int64
+	Key              int64
+	Value            []byte
+}
+
+func (q *Queries) InsertPaymentAttemptFirstHopCustomRecord(ctx context.Context, arg InsertPaymentAttemptFirstHopCustomRecordParams) error {
+	_, err := q.db.ExecContext(ctx, insertPaymentAttemptFirstHopCustomRecord, arg.HtlcAttemptIndex, arg.Key, arg.Value)
+	return err
+}
+
+const insertPaymentDuplicateMig = `-- name: InsertPaymentDuplicateMig :one
+INSERT INTO payment_duplicates (
+    payment_id,
+    amount_msat,
+    created_at,
+    fail_reason,
+    settle_preimage,
+    settle_time
+)
+VALUES (
+    $1,
+    $2,
+    $3,
+    $4,
+    $5,
+    $6
+)
+RETURNING id
+`
+
+type InsertPaymentDuplicateMigParams struct {
+	PaymentID      int64
+	AmountMsat     int64
+	CreatedAt      time.Time
+	FailReason     sql.NullInt32
+	SettlePreimage []byte
+	SettleTime     sql.NullTime
+}
+
+// Insert a duplicate payment record into the payment_duplicates table and
+// return its ID.
+func (q *Queries) InsertPaymentDuplicateMig(ctx context.Context, arg InsertPaymentDuplicateMigParams) (int64, error) {
+	row := q.db.QueryRowContext(ctx, insertPaymentDuplicateMig,
+		arg.PaymentID,
+		arg.AmountMsat,
+		arg.CreatedAt,
+		arg.FailReason,
+		arg.SettlePreimage,
+		arg.SettleTime,
+	)
+	var id int64
+	err := row.Scan(&id)
+	return id, err
+}
+
+const insertPaymentFirstHopCustomRecord = `-- name: InsertPaymentFirstHopCustomRecord :exec
+INSERT INTO payment_first_hop_custom_records (
+    payment_id,
+    key,
+    value
+)
+VALUES (
+    $1,
+    $2,
+    $3
+)
+`
+
+type InsertPaymentFirstHopCustomRecordParams struct {
+	PaymentID int64
+	Key       int64
+	Value     []byte
+}
+
+func (q *Queries) InsertPaymentFirstHopCustomRecord(ctx context.Context, arg InsertPaymentFirstHopCustomRecordParams) error {
+	_, err := q.db.ExecContext(ctx, insertPaymentFirstHopCustomRecord, arg.PaymentID, arg.Key, arg.Value)
+	return err
+}
+
+const insertPaymentHopCustomRecord = `-- name: InsertPaymentHopCustomRecord :exec
+INSERT INTO payment_hop_custom_records (
+    hop_id,
+    key,
+    value
+)
+VALUES (
+    $1,
+    $2,
+    $3
+)
+`
+
+type InsertPaymentHopCustomRecordParams struct {
+	HopID int64
+	Key   int64
+	Value []byte
+}
+
+func (q *Queries) InsertPaymentHopCustomRecord(ctx context.Context, arg InsertPaymentHopCustomRecordParams) error {
+	_, err := q.db.ExecContext(ctx, insertPaymentHopCustomRecord, arg.HopID, arg.Key, arg.Value)
+	return err
+}
+
+const insertPaymentIntent = `-- name: InsertPaymentIntent :one
+INSERT INTO payment_intents (
+    payment_id,
+    intent_type, 
+    intent_payload)
+VALUES (
+    $1,
+    $2, 
+    $3
+)
+RETURNING id
+`
+
+type InsertPaymentIntentParams struct {
+	PaymentID     int64
+	IntentType    int16
+	IntentPayload []byte
+}
+
+// Insert a payment intent for a given payment and return its ID.
+func (q *Queries) InsertPaymentIntent(ctx context.Context, arg InsertPaymentIntentParams) (int64, error) {
+	row := q.db.QueryRowContext(ctx, insertPaymentIntent, arg.PaymentID, arg.IntentType, arg.IntentPayload)
+	var id int64
+	err := row.Scan(&id)
+	return id, err
+}
+
+const insertPaymentMig = `-- name: InsertPaymentMig :one
+/* ─────────────────────────────────────────────
+   Migration-specific queries
+
+   These queries are used ONLY for the one-time migration from KV to SQL.
+   ─────────────────────────────────────────────
+*/
+
+INSERT INTO payments (
+    amount_msat,
+    created_at,
+    payment_identifier,
+    fail_reason)
+VALUES (
+    $1,
+    $2,
+    $3,
+    $4
+)
+RETURNING id
+`
+
+type InsertPaymentMigParams struct {
+	AmountMsat        int64
+	CreatedAt         time.Time
+	PaymentIdentifier []byte
+	FailReason        sql.NullInt32
+}
+
+// Migration-specific payment insert that allows setting fail_reason.
+// Normal InsertPayment forces fail_reason to NULL since new payments
+// aren't failed yet. During migration, we're inserting historical data
+// that may already be failed.
+func (q *Queries) InsertPaymentMig(ctx context.Context, arg InsertPaymentMigParams) (int64, error) {
+	row := q.db.QueryRowContext(ctx, insertPaymentMig,
+		arg.AmountMsat,
+		arg.CreatedAt,
+		arg.PaymentIdentifier,
+		arg.FailReason,
+	)
+	var id int64
+	err := row.Scan(&id)
+	return id, err
+}
+
+const insertRouteHop = `-- name: InsertRouteHop :one
+INSERT INTO payment_route_hops (
+    htlc_attempt_index,
+    hop_index,
+    pub_key,
+    scid,
+    outgoing_time_lock,
+    amt_to_forward,
+    meta_data
+)
+VALUES (
+    $1,
+    $2,
+    $3,
+    $4,
+    $5,
+    $6,
+    $7
+)
+RETURNING id
+`
+
+type InsertRouteHopParams struct {
+	HtlcAttemptIndex int64
+	HopIndex         int32
+	PubKey           []byte
+	Scid             string
+	OutgoingTimeLock int32
+	AmtToForward     int64
+	MetaData         []byte
+}
+
+func (q *Queries) InsertRouteHop(ctx context.Context, arg InsertRouteHopParams) (int64, error) {
+	row := q.db.QueryRowContext(ctx, insertRouteHop,
+		arg.HtlcAttemptIndex,
+		arg.HopIndex,
+		arg.PubKey,
+		arg.Scid,
+		arg.OutgoingTimeLock,
+		arg.AmtToForward,
+		arg.MetaData,
+	)
+	var id int64
+	err := row.Scan(&id)
+	return id, err
+}
+
+const insertRouteHopAmp = `-- name: InsertRouteHopAmp :exec
+INSERT INTO payment_route_hop_amp (
+    hop_id,
+    root_share,
+    set_id,
+    child_index
+)
+VALUES (
+    $1,
+    $2,
+    $3,
+    $4
+)
+`
+
+type InsertRouteHopAmpParams struct {
+	HopID      int64
+	RootShare  []byte
+	SetID      []byte
+	ChildIndex int32
+}
+
+func (q *Queries) InsertRouteHopAmp(ctx context.Context, arg InsertRouteHopAmpParams) error {
+	_, err := q.db.ExecContext(ctx, insertRouteHopAmp,
+		arg.HopID,
+		arg.RootShare,
+		arg.SetID,
+		arg.ChildIndex,
+	)
+	return err
+}
+
+const insertRouteHopBlinded = `-- name: InsertRouteHopBlinded :exec
+INSERT INTO payment_route_hop_blinded (
+    hop_id,
+    encrypted_data,
+    blinding_point,
+    blinded_path_total_amt
+)
+VALUES (
+    $1,
+    $2,
+    $3,
+    $4
+)
+`
+
+type InsertRouteHopBlindedParams struct {
+	HopID               int64
+	EncryptedData       []byte
+	BlindingPoint       []byte
+	BlindedPathTotalAmt sql.NullInt64
+}
+
+func (q *Queries) InsertRouteHopBlinded(ctx context.Context, arg InsertRouteHopBlindedParams) error {
+	_, err := q.db.ExecContext(ctx, insertRouteHopBlinded,
+		arg.HopID,
+		arg.EncryptedData,
+		arg.BlindingPoint,
+		arg.BlindedPathTotalAmt,
+	)
+	return err
+}
+
+const insertRouteHopMpp = `-- name: InsertRouteHopMpp :exec
+INSERT INTO payment_route_hop_mpp (
+    hop_id,
+    payment_addr,
+    total_msat
+)
+VALUES (
+    $1,
+    $2,
+    $3
+)
+`
+
+type InsertRouteHopMppParams struct {
+	HopID       int64
+	PaymentAddr []byte
+	TotalMsat   int64
+}
+
+func (q *Queries) InsertRouteHopMpp(ctx context.Context, arg InsertRouteHopMppParams) error {
+	_, err := q.db.ExecContext(ctx, insertRouteHopMpp, arg.HopID, arg.PaymentAddr, arg.TotalMsat)
+	return err
+}
+
+const settleAttempt = `-- name: SettleAttempt :exec
+INSERT INTO payment_htlc_attempt_resolutions (
+    attempt_index,
+    resolution_time,
+    resolution_type,
+    settle_preimage
+)
+VALUES (
+    $1,
+    $2,
+    $3,
+    $4
+)
+`
+
+type SettleAttemptParams struct {
+	AttemptIndex   int64
+	ResolutionTime time.Time
+	ResolutionType int32
+	SettlePreimage []byte
+}
+
+func (q *Queries) SettleAttempt(ctx context.Context, arg SettleAttemptParams) error {
+	_, err := q.db.ExecContext(ctx, settleAttempt,
+		arg.AttemptIndex,
+		arg.ResolutionTime,
+		arg.ResolutionType,
+		arg.SettlePreimage,
+	)
+	return err
+}