← Watch feed
sqldb/sqlc: simplify non-terminal payment query
Public commit record
68/100 · Adequate
What the developer wrote
Authored by yyforyongyu
sqldb/sqlc: simplify non-terminal payment query
Simplify FetchNonTerminalPayments by collapsing the selector down to
two branches: payments that are not failed and have no settled attempt,
and payments that still have unresolved attempts. This keeps the same
non-terminal semantics while making the query easier to reason about.
Also add a partial index on payments(id) where fail_reason IS NULL to
speed up the startup selector branch that scans payments without a
recorded failure reason.
✓ Descriptive subject✓ Names a concrete action or component✓ Provides detailed explanatory context
What changed, and why it matters
This commit refactors a database query used at startup to find payments that still need attention, and adds a database index to make that query faster. There is no indication of a security vulnerability being fixed; it reads as a performance and code-clarity improvement.
Recommended action
No security action required. Treat as a normal performance/refactor commit. Reviewers may want to verify that the simplified query preserves the original non-terminal semantics across edge cases (e.g., resolution_type values).
Security signals we found
No strong security signals were identified.
Evidence from the diff
The change simplifies FetchNonTerminalPayments into two branches: (1) payments with no fail_reason and no settled attempt (resolution_type=1), and (2) payments with any unresolved HTLC attempt. It also adds a partial index on payments(id) WHERE fail_reason IS NULL. The diff is a pure refactor plus index addition; no input validation, access control, or cryptographic logic is touched.
Changed components
sqldb/sqlc/payments.sql.gosqldb/sqlc/queries/payments.sqlsqldb/sqlc/migrations/000014_payments_no_fail_reason_index.up.sqlsqldb/sqlc/migrations/000014_payments_no_fail_reason_index.down.sqlInspect captured patch +19 / −44
diff --git a/sqldb/sqlc/migrations/000014_payments_no_fail_reason_index.down.sql b/sqldb/sqlc/migrations/000014_payments_no_fail_reason_index.down.sql
new file mode 100644
index 0000000..0301079
--- /dev/null
+++ b/sqldb/sqlc/migrations/000014_payments_no_fail_reason_index.down.sql
@@ -0,0 +1 @@
+DROP INDEX IF EXISTS idx_payments_no_fail_reason;
diff --git a/sqldb/sqlc/migrations/000014_payments_no_fail_reason_index.up.sql b/sqldb/sqlc/migrations/000014_payments_no_fail_reason_index.up.sql
new file mode 100644
index 0000000..97e664b
--- /dev/null
+++ b/sqldb/sqlc/migrations/000014_payments_no_fail_reason_index.up.sql
@@ -0,0 +1,4 @@
+-- Partial index for startup payment recovery queries that filter on
+-- fail_reason IS NULL and walk payment IDs in ascending order.
+CREATE INDEX IF NOT EXISTS idx_payments_no_fail_reason
+ON payments(id) WHERE fail_reason IS NULL;
diff --git a/sqldb/sqlc/payments.sql.go b/sqldb/sqlc/payments.sql.go
index aa52129..2bf4308 100644
--- a/sqldb/sqlc/payments.sql.go
+++ b/sqldb/sqlc/payments.sql.go
@@ -393,39 +393,24 @@ func (q *Queries) FetchHtlcAttemptsForPayments(ctx context.Context, paymentIds [
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
+ SELECT 1 FROM payment_htlc_attempt_resolutions hr
+ JOIN payment_htlc_attempts ha
+ ON ha.attempt_index = hr.attempt_index
WHERE ha.payment_id = p.id
+ AND hr.resolution_type = 1
)
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
+ WHERE NOT EXISTS (
+ SELECT 1 FROM payment_htlc_attempt_resolutions hr
+ WHERE hr.attempt_index = ha.attempt_index
)
)
SELECT
diff --git a/sqldb/sqlc/queries/payments.sql b/sqldb/sqlc/queries/payments.sql
index 6910d1b..68a9126 100644
--- a/sqldb/sqlc/queries/payments.sql
+++ b/sqldb/sqlc/queries/payments.sql
@@ -130,39 +130,24 @@ ORDER BY p.id ASC;
-- 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
+ SELECT 1 FROM payment_htlc_attempt_resolutions hr
+ JOIN payment_htlc_attempts ha
+ ON ha.attempt_index = hr.attempt_index
WHERE ha.payment_id = p.id
+ AND hr.resolution_type = 1
)
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
+ WHERE NOT EXISTS (
+ SELECT 1 FROM payment_htlc_attempt_resolutions hr
+ WHERE hr.attempt_index = ha.attempt_index
)
)
SELECT
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.