Investigate LNbits leaving failed CLN outgoing payments stuck as pending

[macterra]

Summary

LNbits can leave outgoing payments in pending state even after Core Lightning has already marked them failed.

What we observed

• CLN listpays and paystatus show multiple outgoing payments to gondor as failed.
• LNbits still shows those same outgoing payments as pending.
• LNbits logs show this sequence right after payment creation:
  • POST /api/v1/payments returns 201
  • then GET /api/v1/payments/<payment_hash> logs No payments found for payment hash ...
• LNbits SQLite rows in apipayments remain status = 'pending' for those hashes.

Example affected payment hashes

• 4076f2301b5ebed0479e1a131b60a7ae286e9ba4265ea49b18b965838b187908 (avast)
• bfdefdde10f0ad0e79a8a813d4c9e619921e23ace8c4d973da34c30b89841975 (test 3)
• 33f5a21d791357c064eb0c57e0e86c6136f90d6a99aed6e29bf8bde2ee7e47dd (arrrr)

Likely direction

LNbits appears to query payment status before CLN has indexed the outgoing payment, gets a temporary "not found", and fails to reconcile that payment to failed later.

Investigation ideas

• Review LNbits CLNRestWallet outgoing payment status handling for the No payments found path.
• Check whether LNbits should retry or reconcile pending outgoing payments against CLN listpays and paystatus.
• Confirm whether failed outgoing payments are excluded from LNbits' periodic pending-payment reconciliation job.
• Consider marking backend "not found" as retryable instead of keeping the local row pending indefinitely.

Local evidence

From LNbits logs:

• No payments found for payment hash 4076f2301b5ebed0479e1a131b60a7ae286e9ba4265ea49b18b965838b187908
• No payments found for payment hash bfdefdde10f0ad0e79a8a813d4c9e619921e23ace8c4d973da34c30b89841975
• No payments found for payment hash 33f5a21d791357c064eb0c57e0e86c6136f90d6a99aed6e29bf8bde2ee7e47dd

From LNbits SQLite apipayments:

• matching rows still have status = pending

From CLN:

• corresponding outgoing pays are failed, not pending

[santyr]

Here's the version of the wallet backend I'm using which fixes this issue:

https://github.com/santyr/lnbits/blob/dev/lnbits/wallets/clnrest.py