sqldb: InvoiceDB implementation (database, invoices)

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

Host: positiveblue  -  PR author: bhandras

Notes

  • We’re enhancing LND by introducing a new SQL-based database backend, moving beyond the current SQLite and Postgres support that relies on a simple key-value schema. This upgrade enables us to fully leverage the capabilities of SQL databases, allowing for more sophisticated data management and efficiency.

  • This pull request represents a comprehensive update, including modifications to the existing, unreleased schema and queries, and the implementation of a SQL-based InvoiceDB. It also transitions unit test coverage from channeldb and introduces integration tests for the SQL invoice tables, ensuring a robust and reliable database transformation.

Questions

  • How does the LND currently interact with the db to store/load/update its data? How is it different from “native” SQL?
  • What is the relation between PR number #8100 and PR #8052?
  • Some Primary Keys changed to “BIGINT PRIMARY KEY”, how will that work under the hood?
  • What are the main changes in the schema/queries (SQL)?
  • What is an effective mental model for AMP invoices as represented in our database??
  • Why do we define our own errors, like ErrSQLUniqueConstraintViolation?
  • How do we deal with NULL/zero values in our Go and SQL code? Are they always mapped one to one?
  • Man, I hate timezones… anything to add to this comment?
  • What kind of tests do we need to run for the new backends?
  • Why do we have the “skip migrations” flag in our sample config?
  • What should we SQLize next!?