LND

payments/db: normalize LegacyPayload flag in migration comparison

LegacyPayload was a hint used exclusively by the KV store to decide
how to serialize and deserialize the hop payload (legacy format vs
TLV). The SQL store does not serialize hop data at all — every hop
field is persisted natively in its own column — so this flag has no
meaning there and is never stored.

Clear LegacyPayload for all hops inside normalizePaymentForCompare so
that deep-equality checks between KV and SQL payments succeed even when
the KV source data carries LegacyPayload=true.

A dedicated test (TestMigrationLegacyPayloadNormalized) is added to
verify that a payment with LegacyPayload=true hops migrates and
compares correctly.

graph/db/migration1: fix defer commit/rollback in test tx executor

The defer closure checked a local err variable for commit/rollback
decisions, but err remained nil after a successful BeginTx. When
txBody failed, the error was returned directly without assigning to
err, so the defer always committed instead of rolling back.

Additionally, since err was not a named return value, the defer's
Commit error assignment was silently swallowed.

Replace the error-prone defer pattern with explicit rollback on
txBody failure and a direct Commit return.

payments/db: split migration queries into dedicated SQLMigrationQueries interface

sqldb: add migration consistency test

Tests that all migration files follow the defined schema and that
there are not duplicates which could cause collision.

payments/db: fix silent error in duplicate payment lookup

When duplicatePaymentSequenceKey is missing from a duplicate payment
sub-bucket, the code returned the outer function's err variable which
is nil at that point. This caused corrupted duplicate entries to be
silently treated as "not found" instead of failing loudly.

Return a new dedicated ErrNoDuplicateSequenceNumber error so malformed
data is detected immediately.

payments/migration1: handle legacy payments with nil HTLC hash

For legacy payments, the HTLC Hash field may be nil in the bbolt
backend. Previously, the migration would fail with "HTLC attempt X
missing payment hash" when encountering such payments.

This commit fixes the migration by falling back to the parent payment
hash when the HTLC-specific hash is nil. This is consistent with how
the router handles legacy payments (see patchLegacyPaymentHash in
payment_lifecycle.go).

The validation logic is also updated to apply the same fallback when
comparing bbolt data with migrated SQL data, ensuring the comparison
succeeds.

mod: update new direct dependency via go mod tidy

payments/migration1: add external migration test

Add a developer-facing migration_external_test that allows
running the KV→SQL payments migration against a real channel.db
backend to debug migration failures on actual data. The accompanying
testdata README documents how to supply a database file and configure
the test, so users can validate their data and confirm the migration
completes successfully.

The test is skipped by default and meant for manual diagnostics.

payments/migration1: add migration test suite and helpers

Add test helpers plus sql_migration_test coverage for KV→SQL migration.
Basic migration, sequence ordering, data integrity, and feature-specific cases
(MPP/AMP, custom records, blinded routes, metadata, failure messages). Also
cover duplicate payment migration to payment_duplicates, including missing
attempt info to ensure terminal failure is recorded.

This gives broad regression coverage for the migration path and its edge-cases.

payments/migration1: wire KV→SQL migration in the main pkg

Hook the payments KV→SQL migration into the SQL migration config.
The migration is still only available when building with the build tag
"test_native_sql".

Moreover a tombstone protection similar to the invoice migration is added
to prevent re-runningi with the KV backend once migration completes.

sqldb+payments: add payment_duplicates for legacy duplicate payments

Older LND versions could create multiple payments for the same hash.
We need to preserve those historical records during KV→SQL migration,
but they don’t fit the normal payment schema because we enforce a
unique payment hash constraint. Introduce a lean payment_duplicates
table to store only the essential fields (identifier, amount,
timestamps, settle/fail data).

This keeps the primary payment records stable and makes the migration
deterministic even when duplicate records lack attempt info. The table
is intentionally minimal and can be dropped after migration if no
duplicate payments exist.

For now there is no logic in place which allows the noderunner to
fetch duplicate payments after the migration.

payments/migration1: add the payments mig code

Implement the KV→SQL payment migration and add an in-migration
validation pass that deep-compares KV and SQL payment data in batches.
Duplicate payments are migrated into the payment_duplicates table,
and duplicates without attempt info or explicit resolution are marked
failed to ensure terminal state. Validation checks those rows as well.

payments/migration1: freeze core payment code

Copy the core payments/db code into payments/db/migration1 and
add the required sqlc-generated types/queries from sqldb/sqlc.
This effectively freezes the migration code so it stays robust
against future query or schema changes in the main payments package.

Replace the delegation to channeldb.ReadElement/WriteElement with
self-contained, frozen implementations that only handle the exact types
required by this migration package. This removes the dependency on the
live channeldb codec so that future changes to channeldb serialization
cannot silently corrupt or break the migration.

UnknownElementType is also defined locally for the same reason.

paymentsdb+sqldb: add migration related query

Add a migration specific query which allows to set the failure
reason when inserting a payment into the db.

paymentsdb: sort FetchInFlightPayments result by sequence number

The SQL implementation collects payments into a map before converting
to a slice, resulting in non-deterministic iteration order due to Go's
intentional map randomisation. Sort the result by SequenceNum to produce
a deterministic, insertion-ordered output.

Note that the current sole caller (resumePayments in router.go) processes
each payment independently, so this ordering does not affect any existing
behaviour.

paymentsdb: fix duplicate interface check and down migration drop order

- Remove duplicate compile-time interface assertion for SQLStore.

- Fix the down migration to drop payment_intents before payments to
respect the foreign key dependency order. This was not a bug in the
first place bc we have the CASCADE when deleting payments.

paymentsdb: fix SettleAttempt and FailAttempt to use caller-provided timestamps

The SQL backend introduced in this PR was ignoring the SettleTime and
FailTime fields provided in HTLCSettleInfo and HTLCFailInfo, instead
always recording time.Now() as the resolution timestamp. The KV backend
correctly serializes and deserializes these fields.

The timestamps are set by the caller using a mockable clock
(p.router.cfg.Clock.Now() in payment_lifecycle.go), so ignoring them
means the stored timestamp reflects when the DB write happened rather
than when the event occurred, breaking deterministic testing.

This commit also extends the test assertions in assertPaymentInfo to
verify that SettleTime and FailTime are correctly stored and retrieved
by the SQL backend, and updates the relevant call sites to pass explicit
timestamps so regressions are caught.

graphdb: thread topology update context

graphdb: pass context to PruneGraphNodes

graphdb: pass context to PruneTip

graphdb: pass context to GraphSession

graphdb: pass context to PutClosedScid

graphdb: pass context to PruneGraph

graphdb: pass context to IsClosedScid

graphdb: pass context to FilterKnownChanIDs