sqldb: add schema for invoices (table and queries) (sql, database)

https://github.com/lightningnetwork/lnd/pull/7354

Host: positiveblue  -  PR author: positiveblue

Notes

  • The relevant PRs for this series are: https://github.com/lightningnetwork/lnd/pull/7343 https://github.com/lightningnetwork/lnd/pull/7354 https://github.com/lightningnetwork/lnd/pull/7357

  • We will be focusing on the last 2: #7354 & #7357

  • In LND, queries that the db will execute will be written in SQL by the developers (no ORM). We will use sqlc to generate the glue code for executing the queries and parsing the results. You can read more about sqlc https://sqlc.dev/

Questions

General questions:

  • What is the relation between this series of PRs and the (already merged) https://github.com/lightningnetwork/lnd/pull/7215?
  • Didn’t LND already support Postgres? There is also a PR adding support for sqlite, how is this different?
  • Why do we need the ExecTx function defined in lnd/sqldb/sqldb.go

#7354

  • What are the main differences between BOLT11 and AMP invoices?
  • When do we consider an AMP invoice paid?
  • What is the relation between accept_height, expiry_height and resolve_time?
  • Why do we store fields like is_amp in the db? Why can we directly filter using the features column?
  • Can the payment_addr be NULL?
  • Why is payment_request defined as UNIQUE? What happens then with Invoices that can be paid more than once? (AMP, Offers…)
  • Why would we want to keep track of the events related to an invoice?

#7357:

  • Is it possible to directly add an invoice marked as paid with htlcs?
  • What are the different ways that we have to look up an invoice?

  • What kind of updates can we perform on invoice? Are there any updates that are not allowed? Why?

  • Do you see any ways to simplify the InvoiceDB interface/implementation?

  • What’s your overall verdict? (ACK, NACK, concept ACK, unknown)