← Watch feed
sqldb: Change payment_intent relationship to payment table
Public commit record
85/100 · Strong
What the developer wrote
Authored by ziggie
sqldb: Change payment_intent relationship to payment table
Previously a one(intent)-to-many(payment) relationship it is now
changed to a one-to-one relationship because a payment request
only can have 1 payment related to it. Looking into the future
with BOLT12 offers, the fetched invoice from the offer could be
stored here as well and the relationship would still hold.
✓ Specific, descriptive subject✓ Names a concrete action or component✓ Provides detailed explanatory context✓ Explains rationale or failure mode
What changed, and why it matters
This commit restructures how a Lightning Network node stores payment records in its database. It changes the relationship between a 'payment intent' (such as a BOLT 11 invoice) and an actual payment from one intent possibly linked to many payments, to exactly one intent per payment. It also makes the intent record refer to the payment and automatically delete if the payment is deleted. There is no direct security vulnerability visible in the diff, but any schema migration in a financial database needs careful review for data integrity and correctness.
Recommended action
Treat this as a schema-correctness change rather than a security patch. Reviewers should verify that existing deployments running the previous version of migration 000009 are safely upgraded (the migration is idempotent via IF NOT EXISTS but the dropped columns/indexes may not be removed on already-created databases), confirm that removing intent payload deduplication does not allow duplicate payments for the same invoice, and run regression tests on payment fetching and deletion. No immediate security response is indicated.
Security signals we found
01
Database schema migration altering foreign-key relationships in payment subsystem
02
Removal of unique deduplication index on payment intent payload
03
Addition of ON DELETE CASCADE between payment_intents and payments
04
No changes to SQL injection boundaries, query parameterization, or access control
Evidence from the diff
The patch modifies the SQL schema migration for the payments subsystem in LND’s new SQLite-based SQLDB backend. It removes the nullable intent_id foreign key from the payments table, drops the standalone payment_intents table with its unique index on (intent_type, intent_payload), and recreates payment_intents with a non-nullable payment_id foreign key referencing payments(id) ON DELETE CASCADE plus a UNIQUE constraint on payment_id. Generated Go models and sqlc queries are updated to join on payment_intents.payment_id instead of payments.intent_id. The change enforces a one-to-one relationship and removes the previous deduplication-by-intent-payload behavior. No input validation, authorization, or cryptographic logic is changed.
Changed components
sqldb/sqlc/migrations/000009_payments.up.sqlsqldb/sqlc/models.gosqldb/sqlc/payments.sql.gosqldb/sqlc/queries/payments.sqlInspect captured patch +53 / −52
diff --git a/sqldb/sqlc/migrations/000009_payments.up.sql b/sqldb/sqlc/migrations/000009_payments.up.sql
index 0d85b49..65094a1 100644
--- a/sqldb/sqlc/migrations/000009_payments.up.sql
+++ b/sqldb/sqlc/migrations/000009_payments.up.sql
@@ -2,43 +2,12 @@
-- Payment System Schema Migration
-- ─────────────────────────────────────────────
-- This migration creates the complete payment system schema including:
--- - Payment intents (BOLT 11/12 invoices, offers)
+-- - Payment intents (only BOLT 11 invoices for now)
-- - Payment attempts and HTLC tracking
-- - Route hops and custom TLV records
-- - Resolution tracking for settled/failed payments
-- ─────────────────────────────────────────────
--- ─────────────────────────────────────────────
--- Payment Intents Table
--- ─────────────────────────────────────────────
--- Stores the descriptor of what the payment is paying for.
--- Depending on the type, the payload might contain:
--- - BOLT 11 invoice data
--- - BOLT 12 offer data
--- - NULL for legacy hash-only/keysend style payments
--- ─────────────────────────────────────────────
-
-CREATE TABLE IF NOT EXISTS payment_intents (
- -- Primary key for the intent record
- id INTEGER PRIMARY KEY,
-
- -- The type of intent (e.g. 0 = bolt11_invoice, 1 = bolt12_offer)
- -- Uses SMALLINT (int16) for efficient storage of enum values
- intent_type SMALLINT NOT NULL,
-
- -- The serialized payload for the payment intent
- -- Content depends on type - could be invoice, offer, or NULL
- intent_payload BLOB
-);
-
--- Index for efficient querying by intent type
-CREATE INDEX IF NOT EXISTS idx_payment_intents_type
-ON payment_intents(intent_type);
-
--- Unique constraint for deduplication of payment intents
-CREATE UNIQUE INDEX IF NOT EXISTS idx_payment_intents_unique
-ON payment_intents(intent_type, intent_payload);
-
-- ─────────────────────────────────────────────
-- Payments Table
-- ─────────────────────────────────────────────
@@ -55,10 +24,6 @@ CREATE TABLE IF NOT EXISTS payments (
-- Primary key for the payment record
id INTEGER PRIMARY KEY,
- -- Optional reference to the payment intent this payment was derived from
- -- Links to BOLT 11 invoice, BOLT 12 offer, etc.
- intent_id BIGINT REFERENCES payment_intents (id),
-
-- The amount of the payment in millisatoshis
amount_msat BIGINT NOT NULL,
@@ -70,20 +35,59 @@ CREATE TABLE IF NOT EXISTS payments (
-- For AMP: the setID
-- For future intent types: any unique payment-level key
payment_identifier BLOB NOT NULL,
-
+
-- The reason for payment failure (only set if payment has failed)
-- Integer enum type indicating failure reason
fail_reason INTEGER,
-- Ensure payment identifiers are unique across all payments
- CONSTRAINT idx_payments_payment_identifier_unique
+ CONSTRAINT idx_payments_payment_identifier_unique
UNIQUE (payment_identifier)
);
-- Index for efficient querying by creation time (for chronological ordering)
-CREATE INDEX IF NOT EXISTS idx_payments_created_at
+CREATE INDEX IF NOT EXISTS idx_payments_created_at
ON payments(created_at);
+-- ─────────────────────────────────────────────
+-- Payment Intents Table
+-- ─────────────────────────────────────────────
+-- Stores the descriptor of what the payment is paying for.
+-- Depending on the type, the payload might contain:
+-- - BOLT 11 invoice data
+-- - BOLT 12 offer data
+-- - NULL for legacy hash-only/keysend style payments
+-- ─────────────────────────────────────────────
+
+CREATE TABLE IF NOT EXISTS payment_intents (
+ -- Primary key for the intent record
+ id INTEGER PRIMARY KEY,
+
+ -- Reference to the payment this intent belongs to (one-to-one relationship)
+ -- When the payment is deleted, the intent is automatically deleted
+ payment_id BIGINT NOT NULL REFERENCES payments (id) ON DELETE CASCADE,
+
+ -- The type of intent (e.g. 0 = bolt11_invoice, 1 = bolt12_invoice)
+ -- Uses SMALLINT (int16) for efficient storage of enum values
+ intent_type SMALLINT NOT NULL,
+
+ -- The serialized payload for the payment intent
+ -- Content depends on type - could be invoice, offer, or NULL
+ intent_payload BLOB,
+
+ -- Ensure one-to-one relationship: each payment has at most one intent.
+ -- Currently we only support one intent per payment this makes sure we do
+ -- not accidentally pay the same request multiple times. This currently
+ -- only has bolt 11 payment requests/invoices. But in the future this can
+ -- also include BOLT 12 offers/invoices.
+ CONSTRAINT idx_payment_intents_payment_id_unique
+ UNIQUE (payment_id)
+);
+
+-- Index for efficient querying by intent type
+CREATE INDEX IF NOT EXISTS idx_payment_intents_type
+ON payment_intents(intent_type);
+
-- ─────────────────────────────────────────────
-- Payment HTLC Attempts Table
-- ─────────────────────────────────────────────
diff --git a/sqldb/sqlc/models.go b/sqldb/sqlc/models.go
index 899c572..0e04cd4 100644
--- a/sqldb/sqlc/models.go
+++ b/sqldb/sqlc/models.go
@@ -211,7 +211,6 @@ type MigrationTracker struct {
type Payment struct {
ID int64
- IntentID sql.NullInt64
AmountMsat int64
CreatedAt time.Time
PaymentIdentifier []byte
@@ -264,6 +263,7 @@ type PaymentHtlcAttemptResolution struct {
type PaymentIntent struct {
ID int64
+ PaymentID int64
IntentType int16
IntentPayload []byte
}
diff --git a/sqldb/sqlc/payments.sql.go b/sqldb/sqlc/payments.sql.go
index 83c1c7f..e28e8a5 100644
--- a/sqldb/sqlc/payments.sql.go
+++ b/sqldb/sqlc/payments.sql.go
@@ -317,11 +317,11 @@ func (q *Queries) FetchHtlcAttemptsForPayments(ctx context.Context, paymentIds [
const fetchPayment = `-- name: FetchPayment :one
SELECT
- p.id, p.intent_id, p.amount_msat, p.created_at, p.payment_identifier, p.fail_reason,
+ 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.id = p.intent_id
+LEFT JOIN payment_intents i ON i.payment_id = p.id
WHERE p.payment_identifier = $1
`
@@ -336,7 +336,6 @@ func (q *Queries) FetchPayment(ctx context.Context, paymentIdentifier []byte) (F
var i FetchPaymentRow
err := row.Scan(
&i.Payment.ID,
- &i.Payment.IntentID,
&i.Payment.AmountMsat,
&i.Payment.CreatedAt,
&i.Payment.PaymentIdentifier,
@@ -398,11 +397,11 @@ func (q *Queries) FetchPaymentLevelFirstHopCustomRecords(ctx context.Context, pa
const fetchPaymentsByIDs = `-- name: FetchPaymentsByIDs :many
SELECT
- p.id, p.intent_id, p.amount_msat, p.created_at, p.payment_identifier, p.fail_reason,
+ 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.id = p.intent_id
+LEFT JOIN payment_intents i ON i.payment_id = p.id
WHERE p.id IN (/*SLICE:payment_ids*/?)
`
@@ -433,7 +432,6 @@ func (q *Queries) FetchPaymentsByIDs(ctx context.Context, paymentIds []int64) ([
var i FetchPaymentsByIDsRow
if err := rows.Scan(
&i.Payment.ID,
- &i.Payment.IntentID,
&i.Payment.AmountMsat,
&i.Payment.CreatedAt,
&i.Payment.PaymentIdentifier,
@@ -510,11 +508,11 @@ const filterPayments = `-- name: FilterPayments :many
*/
SELECT
- p.id, p.intent_id, p.amount_msat, p.created_at, p.payment_identifier, p.fail_reason,
+ 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.id = p.intent_id
+LEFT JOIN payment_intents i ON i.payment_id = p.id
WHERE (
p.id > $1 OR
$1 IS NULL
@@ -572,7 +570,6 @@ func (q *Queries) FilterPayments(ctx context.Context, arg FilterPaymentsParams)
var i FilterPaymentsRow
if err := rows.Scan(
&i.Payment.ID,
- &i.Payment.IntentID,
&i.Payment.AmountMsat,
&i.Payment.CreatedAt,
&i.Payment.PaymentIdentifier,
diff --git a/sqldb/sqlc/queries/payments.sql b/sqldb/sqlc/queries/payments.sql
index ce43a3e..a94ba1f 100644
--- a/sqldb/sqlc/queries/payments.sql
+++ b/sqldb/sqlc/queries/payments.sql
@@ -9,7 +9,7 @@ SELECT
i.intent_type AS "intent_type",
i.intent_payload AS "intent_payload"
FROM payments p
-LEFT JOIN payment_intents i ON i.id = p.intent_id
+LEFT JOIN payment_intents i ON i.payment_id = p.id
WHERE (
p.id > sqlc.narg('index_offset_get') OR
sqlc.narg('index_offset_get') IS NULL
@@ -37,7 +37,7 @@ SELECT
i.intent_type AS "intent_type",
i.intent_payload AS "intent_payload"
FROM payments p
-LEFT JOIN payment_intents i ON i.id = p.intent_id
+LEFT JOIN payment_intents i ON i.payment_id = p.id
WHERE p.payment_identifier = $1;
-- name: FetchPaymentsByIDs :many
@@ -46,7 +46,7 @@ SELECT
i.intent_type AS "intent_type",
i.intent_payload AS "intent_payload"
FROM payments p
-LEFT JOIN payment_intents i ON i.id = p.intent_id
+LEFT JOIN payment_intents i ON i.payment_id = p.id
WHERE p.id IN (sqlc.slice('payment_ids')/*SLICE:payment_ids*/);
-- name: CountPayments :one
Risk score
Our methodology →Why this scored 17/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.