sqldb: add queries for deleting a payment and attempts
What the developer wrote
Authored by ziggie
sqldb: add queries for deleting a payment and attempts
What changed, and why it matters
This commit adds two new database helper functions for deleting payment records and failed payment attempts in LND's SQL backend. It is purely an infrastructure change: the new functions are defined but not yet called anywhere in the code, so it cannot by itself cause data loss, unauthorized deletion, or any other security issue. It simply prepares the database layer for future features such as payment cleanup or pruning.
No immediate action required. When these helpers are later wired into user-facing RPCs or automated pruning, ensure: (1) the caller manages transactions and foreign-key/ON DELETE behavior for child tables such as payment_htlc_attempts, payment_htlc_attempt_resolutions, payment_hops, and custom records; (2) access is authorized; (3) the magic number 2 for resolution_type is documented or replaced with a named constant; and (4) deletion is auditable.
Security signals we found
New DELETE SQL queries added to payments subsystem
No callers or authorization logic present in this commit
Potential future concern: cascading deletion of related payment_htlc_attempts, custom records, and other child tables not handled here
Potential future concern: hard-coded resolution_type = 2 magic number for failed attempts
Evidence from the diff
The patch introduces DeletePayment and DeleteFailedAttempts SQL queries and their generated Go wrappers. DeletePayment removes a row from the payments table by id. DeleteFailedAttempts removes rows from payment_htlc_attempts where payment_id matches and the attempt is recorded in payment_htlc_attempt_resolutions with resolution_type = 2 (failed). The queries are added to the SQLQueries/Querier interfaces and the SQL store interface, but no business logic invokes them yet. There are no authorization checks, transaction handling, or cascading-delete concerns visible in this commit, but those would be the responsibility of whatever code eventually calls these helpers.
Changed components
payments/db/sql_store.gosqldb/sqlc/payments.sql.gosqldb/sqlc/querier.gosqldb/sqlc/queries/payments.sqlInspect captured patch +42 / −0
diff --git a/payments/db/sql_store.go b/payments/db/sql_store.go
index 72fac39..4e494c8 100644
--- a/payments/db/sql_store.go
+++ b/payments/db/sql_store.go
@@ -55,6 +55,16 @@ type SQLQueries interface {
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.
+ */
+
+ 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
}
// BatchedSQLQueries is a version of the SQLQueries that's capable
diff --git a/sqldb/sqlc/payments.sql.go b/sqldb/sqlc/payments.sql.go
index e28e8a5..ae92aa1 100644
--- a/sqldb/sqlc/payments.sql.go
+++ b/sqldb/sqlc/payments.sql.go
@@ -23,6 +23,26 @@ func (q *Queries) CountPayments(ctx context.Context) (int64, error) {
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
+)
+`
+
+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 fetchAllInflightAttempts = `-- name: FetchAllInflightAttempts :many
SELECT
ha.id,
diff --git a/sqldb/sqlc/querier.go b/sqldb/sqlc/querier.go
index f4c7673..d1605a0 100644
--- a/sqldb/sqlc/querier.go
+++ b/sqldb/sqlc/querier.go
@@ -22,11 +22,13 @@ type Querier interface {
DeleteChannelPolicyExtraTypes(ctx context.Context, channelPolicyID int64) error
DeleteChannels(ctx context.Context, ids []int64) error
DeleteExtraNodeType(ctx context.Context, arg DeleteExtraNodeTypeParams) error
+ DeleteFailedAttempts(ctx context.Context, paymentID int64) error
DeleteInvoice(ctx context.Context, arg DeleteInvoiceParams) (sql.Result, error)
DeleteNode(ctx context.Context, id int64) error
DeleteNodeAddresses(ctx context.Context, nodeID int64) error
DeleteNodeByPubKey(ctx context.Context, arg DeleteNodeByPubKeyParams) (sql.Result, error)
DeleteNodeFeature(ctx context.Context, arg DeleteNodeFeatureParams) error
+ DeletePayment(ctx context.Context, id int64) error
DeletePruneLogEntriesInRange(ctx context.Context, arg DeletePruneLogEntriesInRangeParams) error
DeleteUnconnectedNodes(ctx context.Context) ([][]byte, error)
DeleteZombieChannel(ctx context.Context, arg DeleteZombieChannelParams) (sql.Result, error)
diff --git a/sqldb/sqlc/queries/payments.sql b/sqldb/sqlc/queries/payments.sql
index a94ba1f..a70631a 100644
--- a/sqldb/sqlc/queries/payments.sql
+++ b/sqldb/sqlc/queries/payments.sql
@@ -151,3 +151,13 @@ FROM payment_hop_custom_records l
WHERE l.hop_id IN (sqlc.slice('hop_ids')/*SLICE:hop_ids*/)
ORDER BY l.hop_id ASC, l.key ASC;
+
+-- name: DeletePayment :exec
+DELETE FROM payments WHERE id = $1;
+
+-- name: DeleteFailedAttempts :exec
+-- Delete all failed HTLC attempts for the given payment. Resolution type 2
+-- indicates a failed attempt.
+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
+);
Why this scored 12/100
Community notes
Notes can correct, qualify, or add evidence to the AI analysis. Every note shown here has been validated by a human moderator.
The AI analysis stands alone for now. Submit a note if you can add evidence or important context.