sqldb: `InvoiceDB` implementation

[bhandras]

This PR is based on the work of @positiveblue in https://github.com/lightningnetwork/lnd/pull/7357. Also based on top of https://github.com/lightningnetwork/lnd/pull/8100 which is preliminary work refactoring the update invoice flow in order to be able to port InvoiceDB to SQL.

The PR is a collection of changes to the existing (but unreleased) schema and queries and implementation of a SQL based InvoiceDB utilizing the existing unit test coverage (moved from channeldb) and adding support for running the integration tests with native SQL invoice tables.

The PR does not implement migration for existing nodes!

To test locally, start a new regtest node with --db.backend=sqlite --db.use-native-sql flags.

[bhandras]

Still a WIP draft, but now the SQL InvoiceStore works and so ready for some very early look.

[coderabbitai[bot]]

[!IMPORTANT]

Auto Review Skipped

Auto reviews are limited to the following labels: llm-review. Please add one of these labels to enable auto reviews.

Please check the settings in the CodeRabbit UI or the .coderabbit.yaml file in this repository.

To trigger a single review, invoke the @coderabbitai review command.

Thank you for using CodeRabbit. We offer it for free to the OSS community and would appreciate your support in helping us grow. If you find it useful, would you consider giving us a shout-out on your favorite social media?

Share

• X
• Mastodon
• Reddit
• LinkedIn

---

Tips

Chat

There are 3 ways to chat with CodeRabbit:

• Review comments: Directly reply to a review comment made by CodeRabbit. Example:
  • I pushed a fix in commit <commit_id>.
  • Generate unit-tests for this file.
• Files and specific lines of code (under the "Files changed" tab): Tag @coderabbitai in a new review comment at the desired location with your query. Examples:
  • @coderabbitai generate unit tests for this file.
  • @coderabbitai modularize this function.
• PR comments: Tag @coderabbitai in a new PR comment to ask questions about the PR branch. For the best results, please provide a very specific query, as very limited context is provided in this mode. Examples:
  • @coderabbitai generate interesting stats about this repository from git and render them as a table.
  • @coderabbitai show all the console.log statements in this repository.
  • @coderabbitai read src/utils.ts and generate unit tests.
  • @coderabbitai read the files in the src/scheduler package and generate a class diagram using mermaid and a README in the markdown format.

Note: Be mindful of the bot's finite context window. It's strongly recommended to break down tasks such as reading entire modules into smaller chunks. For a focused discussion, use review comments to chat about specific files and their changes, instead of using the PR comments.

CodeRabbit Commands (invoked as PR comments)

• @coderabbitai pause to pause the reviews on a PR.
• @coderabbitai resume to resume the paused reviews.
• @coderabbitai review to trigger a review. This is useful when automatic reviews are disabled for the repository.
• @coderabbitai resolve resolve all the CodeRabbit review comments.
• @coderabbitai help to get help.

Additionally, you can add @coderabbitai ignore anywhere in the PR description to prevent this PR from being reviewed.

CodeRabbit Configration File (.coderabbit.yaml)

• You can programmatically configure CodeRabbit by adding a .coderabbit.yaml file to the root of your repository.
• The JSON schema for the configuration file is available here.
• If your editor has YAML language server enabled, you can add the path at the top of this file to enable auto-completion and validation: # yaml-language-server: $schema=https://coderabbit.ai/integrations/coderabbit-overrides.v2.json

CodeRabbit Discord Community

Join our Discord Community to get help, request features, and share feedback.

[ziggie1984]

I think we should fail when starting an already existing bbolt database with an explicit error because currently we will allow to change the backends but fails down the road with an kind of unrelated error (it is not 100% unrelated but it disguises the real reason imo):

Current logs:

2024-02-01 17:39:45.164 [INF] LTND: Opening the main database, this might take a few minutes...
2024-02-01 17:39:45.170 [WRN] LTND: Found existing bbolt database file in wallet.db while using database type sqlite. Existing data will NOT be migrated to sqlite automatically!
2024-02-01 17:39:45.170 [WRN] LTND: Found existing bbolt database file in channel.db while using database type sqlite. Existing data will NOT be migrated to sqlite automatically!

and fails only when trying to swap the channel backup:

[ERR] LTND: Shutting down because error in main method: unable to start server: unable to refresh backup file: unable to extract on disk encrypted SCB: chacha20poly1305: message authentication failed

I think we should fails immediately when figuring out that a db is already there.

EDIT: Reviewing it currently but this comment kind of did not fit into the review flow 😅

[Roasbeef]

I think we should reverse the relationship, or better, remove table invoice_features and store the feature bits in table invoices.

I think reversing can make sense there. Then we can reduce the amount of redundant data stored, as most of our invoices have the exact same set of features. So we can store just the feature table flat, then reference the feature table within the invoices.

[Roasbeef]

Re diagrams, I've used this tool in the past (sample from an early version of the tapd tables), it also can take in direct SQL as well (see import): https://dbdiagram.io/d/62abad509921fe2a96234f8a

[Roasbeef]

@positiveblue

So we may not have to add the payment_address parameter here.

(might be slightly out of context) So the ListInvoices RPC call can also supply the payment_addr to obtain all the sub-invoices for a given AMP invoice. In theory we could generalize the terminology a bit here, to also prep for BOLT 12 Offers which has similar concept of sub-invoices (main one is the offer, then each send to that is basically a new sub-invoice).

[Roasbeef]

Also for reviewers, note that this is actually built on top of: https://github.com/lightningnetwork/lnd/pull/8100

Perusing the prior comments may help us save some time and avoid rehashing old threads or design iterations.

[lightninglabs-deploy]

@roasbeef: review reminder @yyforyongyu: review reminder @ziggie1984: review reminder @positiveblue: review reminder

[bhandras]

@Roasbeef: review reminder @yyforyongyu: review reminder @ziggie1984: review reminder @positiveblue: review reminder

Rebased on master after the merge of #8100. PTAL :)

[bhandras]

Oh LGTM vanished once i answered in reviewable 😅 ptal @Roasbeef