Public commit record
78/100 · Adequate
What the developer wrote
Authored by yyforyongyu
sqldb/sqlc: add non-terminal payment query
FetchInFlightPayments needs a dedicated SQL query that can return
non-terminal payments without relying on the unresolved-attempt scan.
The first version of that query fixed correctness, but the follow-up
selector measurements showed a UNION-based shape was materially
faster while returning the same payment set.
Use the inflight regression tests as evidence. The tests still fail on
SQL before the Go payment store is wired up, but this commit adds the
final SQL surface the later wiring commit depends on.
Add FetchNonTerminalPayments to the SQL query set, regenerate the sqlc
bindings, add the PaymentAndIntent adapters for the new row type, and
use the UNION-based candidate selection so the final query shape lands
in one commit.
✓ Descriptive subject✓ Names a concrete action or component✓ Provides detailed explanatory context✓ Mentions testing or verification
What changed, and why it matters
This commit adds a new database query to the Lightning Network Daemon (LND) for finding payments that haven't finished yet. It is a performance and correctness improvement to how the node tracks in-flight payments. There is no indication it fixes a security vulnerability or introduces a new attack path.
Recommended action
No security action required. Treat as a normal database-layer refactor. If reviewing the broader PR, verify the follow-up wiring commit correctly uses FetchNonTerminalPayments and that the UNION query's semantics match the intended non-terminal definition across all database backends.
Security signals we found
01
No security-relevant keywords in commit title or message
02
No input is concatenated into SQL; query uses parameterized pagination ($1, $2)
03
No new external interfaces, RPCs, or permissions introduced
04
No changes to authentication, authorization, cryptography, or networking
05
Commit is framed as a performance/correctness refactor, not a security fix
Evidence from the diff
The change introduces FetchNonTerminalPayments, a SQL query that returns payments which are still in progress (non-terminal). It replaces or supplements an earlier unresolved-attempt scan with a UNION-based query that the commit message says is materially faster while returning the same result set. The patch adds the SQL query, regenerates sqlc bindings, and adds Go adapter methods so the new row type satisfies the PaymentAndIntent interface. No runtime logic that uses this query is added in this commit; it only exposes the SQL surface.
Changed components
payments/db/sql_store.gosqldb/sqlc/db_custom.gosqldb/sqlc/payments.sql.gosqldb/sqlc/querier.gosqldb/sqlc/queries/payments.sqlInspect captured patch +195 / −0
diff --git a/payments/db/sql_store.go b/payments/db/sql_store.go
index 4c4f0f3..321c4b9 100644
--- a/payments/db/sql_store.go
+++ b/payments/db/sql_store.go
@@ -50,6 +50,7 @@ type SQLQueries interface {
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)
+ FetchNonTerminalPayments(ctx context.Context, arg sqlc.FetchNonTerminalPaymentsParams) ([]sqlc.FetchNonTerminalPaymentsRow, error)
CountPayments(ctx context.Context) (int64, error)
diff --git a/sqldb/sqlc/db_custom.go b/sqldb/sqlc/db_custom.go
index 1b8d465..b8d4766 100644
--- a/sqldb/sqlc/db_custom.go
+++ b/sqldb/sqlc/db_custom.go
@@ -241,3 +241,32 @@ func (r FetchPaymentsByIDsRow) GetPaymentIntent() PaymentIntent {
IntentPayload: r.IntentPayload,
}
}
+
+// GetPayment returns the Payment associated with this interface.
+//
+// NOTE: This method is part of the PaymentAndIntent interface.
+func (r FetchNonTerminalPaymentsRow) GetPayment() Payment {
+ return Payment{
+ ID: r.ID,
+ AmountMsat: r.AmountMsat,
+ CreatedAt: r.CreatedAt,
+ PaymentIdentifier: r.PaymentIdentifier,
+ FailReason: r.FailReason,
+ }
+}
+
+// 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 FetchNonTerminalPaymentsRow) GetPaymentIntent() PaymentIntent {
+ if !r.IntentType.Valid {
+ return PaymentIntent{}
+ }
+
+ return PaymentIntent{
+ IntentType: r.IntentType.Int16,
+ IntentPayload: r.IntentPayload,
+ }
+}
diff --git a/sqldb/sqlc/payments.sql.go b/sqldb/sqlc/payments.sql.go
index f5b76b8..5afc7a1 100644
--- a/sqldb/sqlc/payments.sql.go
+++ b/sqldb/sqlc/payments.sql.go
@@ -454,6 +454,110 @@ func (q *Queries) FetchHtlcAttemptsForPayments(ctx context.Context, paymentIds [
return items, nil
}
+const fetchNonTerminalPayments = `-- name: FetchNonTerminalPayments :many
+WITH non_terminal_ids AS (
+ SELECT ha.payment_id AS id
+ FROM payment_htlc_attempts ha
+ WHERE NOT EXISTS (
+ SELECT 1 FROM payment_htlc_attempt_resolutions hr
+ WHERE hr.attempt_index = ha.attempt_index
+ )
+
+ UNION
+
+ SELECT p.id
+ FROM payments p
+ WHERE p.fail_reason IS NULL
+ AND NOT EXISTS (
+ SELECT 1 FROM payment_htlc_attempts ha
+ WHERE ha.payment_id = p.id
+ )
+
+ UNION
+
+ SELECT DISTINCT ha.payment_id AS id
+ FROM payment_htlc_attempts ha
+ JOIN payment_htlc_attempt_resolutions hr
+ ON hr.attempt_index = ha.attempt_index
+ JOIN payments p
+ ON p.id = ha.payment_id
+ WHERE p.fail_reason IS NULL
+ AND hr.resolution_type = 2
+ AND NOT EXISTS (
+ SELECT 1 FROM payment_htlc_attempts ha2
+ JOIN payment_htlc_attempt_resolutions hr2
+ ON hr2.attempt_index = ha2.attempt_index
+ WHERE ha2.payment_id = ha.payment_id
+ AND hr2.resolution_type = 1
+ )
+)
+SELECT
+ p.id,
+ p.amount_msat,
+ p.created_at,
+ p.payment_identifier,
+ p.fail_reason,
+ pi.intent_type,
+ pi.intent_payload
+FROM non_terminal_ids n
+JOIN payments p
+ ON p.id = n.id
+LEFT JOIN payment_intents pi
+ ON pi.payment_id = p.id
+WHERE p.id > $1
+ORDER BY p.id ASC
+LIMIT $2
+`
+
+type FetchNonTerminalPaymentsParams struct {
+ ID int64
+ Limit int32
+}
+
+type FetchNonTerminalPaymentsRow struct {
+ ID int64
+ AmountMsat int64
+ CreatedAt time.Time
+ PaymentIdentifier []byte
+ FailReason sql.NullInt32
+ IntentType sql.NullInt16
+ IntentPayload []byte
+}
+
+// Fetch all non-terminal payments using pagination. A payment is
+// non-terminal if it has an unresolved attempt, or if it has not been
+// permanently failed and has no settled attempt yet.
+func (q *Queries) FetchNonTerminalPayments(ctx context.Context, arg FetchNonTerminalPaymentsParams) ([]FetchNonTerminalPaymentsRow, error) {
+ rows, err := q.db.QueryContext(ctx, fetchNonTerminalPayments, arg.ID, arg.Limit)
+ if err != nil {
+ return nil, err
+ }
+ defer rows.Close()
+ var items []FetchNonTerminalPaymentsRow
+ for rows.Next() {
+ var i FetchNonTerminalPaymentsRow
+ 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 fetchPayment = `-- name: FetchPayment :one
SELECT
p.id, p.amount_msat, p.created_at, p.payment_identifier, p.fail_reason,
diff --git a/sqldb/sqlc/querier.go b/sqldb/sqlc/querier.go
index a810c60..b2f4633 100644
--- a/sqldb/sqlc/querier.go
+++ b/sqldb/sqlc/querier.go
@@ -50,6 +50,10 @@ type Querier interface {
// group the resolutions by payment_id in the background.
FetchHtlcAttemptResolutionsForPayments(ctx context.Context, paymentIds []int64) ([]FetchHtlcAttemptResolutionsForPaymentsRow, error)
FetchHtlcAttemptsForPayments(ctx context.Context, paymentIds []int64) ([]FetchHtlcAttemptsForPaymentsRow, error)
+ // Fetch all non-terminal payments using pagination. A payment is
+ // non-terminal if it has an unresolved attempt, or if it has not been
+ // permanently failed and has no settled attempt yet.
+ FetchNonTerminalPayments(ctx context.Context, arg FetchNonTerminalPaymentsParams) ([]FetchNonTerminalPaymentsRow, error)
FetchPayment(ctx context.Context, paymentIdentifier []byte) (FetchPaymentRow, error)
// Fetch all duplicate payment records from the payment_duplicates table for
// a given payment ID.
diff --git a/sqldb/sqlc/queries/payments.sql b/sqldb/sqlc/queries/payments.sql
index 6c0a544..6473ef6 100644
--- a/sqldb/sqlc/queries/payments.sql
+++ b/sqldb/sqlc/queries/payments.sql
@@ -148,6 +148,63 @@ AND ha.attempt_index > $1
ORDER BY ha.attempt_index ASC
LIMIT $2;
+-- name: FetchNonTerminalPayments :many
+-- Fetch all non-terminal payments using pagination. A payment is
+-- non-terminal if it has an unresolved attempt, or if it has not been
+-- permanently failed and has no settled attempt yet.
+WITH non_terminal_ids AS (
+ SELECT ha.payment_id AS id
+ FROM payment_htlc_attempts ha
+ WHERE NOT EXISTS (
+ SELECT 1 FROM payment_htlc_attempt_resolutions hr
+ WHERE hr.attempt_index = ha.attempt_index
+ )
+
+ UNION
+
+ SELECT p.id
+ FROM payments p
+ WHERE p.fail_reason IS NULL
+ AND NOT EXISTS (
+ SELECT 1 FROM payment_htlc_attempts ha
+ WHERE ha.payment_id = p.id
+ )
+
+ UNION
+
+ SELECT DISTINCT ha.payment_id AS id
+ FROM payment_htlc_attempts ha
+ JOIN payment_htlc_attempt_resolutions hr
+ ON hr.attempt_index = ha.attempt_index
+ JOIN payments p
+ ON p.id = ha.payment_id
+ WHERE p.fail_reason IS NULL
+ AND hr.resolution_type = 2
+ AND NOT EXISTS (
+ SELECT 1 FROM payment_htlc_attempts ha2
+ JOIN payment_htlc_attempt_resolutions hr2
+ ON hr2.attempt_index = ha2.attempt_index
+ WHERE ha2.payment_id = ha.payment_id
+ AND hr2.resolution_type = 1
+ )
+)
+SELECT
+ p.id,
+ p.amount_msat,
+ p.created_at,
+ p.payment_identifier,
+ p.fail_reason,
+ pi.intent_type,
+ pi.intent_payload
+FROM non_terminal_ids n
+JOIN payments p
+ ON p.id = n.id
+LEFT JOIN payment_intents pi
+ ON pi.payment_id = p.id
+WHERE p.id > $1
+ORDER BY p.id ASC
+LIMIT $2;
+
-- name: FetchHopsForAttempts :many
SELECT
h.id,
Risk score
Our methodology →Why this scored 12/100
Human-validated context
Community notes
Notes can correct, qualify, or add evidence to the AI analysis. Every note shown here has been validated by a human moderator.
No validated notes yet.
The AI analysis stands alone for now. Submit a note if you can add evidence or important context.