lnd
lnd copied to clipboard
lightningnetwork
[Part 1|3] Introduce SQL Payment schema into LND
Created a draft of the schema, will now proceed to create the whole sqlc harness now. If you have already feedback feel free to reach out.
Needs the refactor series as a prerequestie:
Depends on:
- #9822
- #9825
- #9826
[!IMPORTANT]
Review skipped
Auto reviews are limited to specific labels.
:label: Labels to auto review (1)
- llm-review
Please check the settings in the CodeRabbit UI or the
.coderabbit.yamlfile in this repository. To trigger a single review, invoke the@coderabbitai reviewcommand.You can disable this status message by setting the
reviews.review_statustofalsein the CodeRabbit configuration file.
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?
🪧 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>, please review it.Generate unit testing code for this file.Open a follow-up GitHub issue for this discussion.
- Files and specific lines of code (under the "Files changed" tab): Tag
@coderabbitaiin a new review comment at the desired location with your query. Examples:@coderabbitai generate unit testing code for this file.@coderabbitai modularize this function.
- PR comments: Tag
@coderabbitaiin 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 gather interesting stats about this repository and render them as a table. Additionally, render a pie chart showing the language distribution in the codebase.@coderabbitai read src/utils.ts and generate unit testing code.@coderabbitai read the files in the src/scheduler package and generate a class diagram using mermaid and a README in the markdown format.@coderabbitai help me debug CodeRabbit configuration file.
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 using PR comments)
@coderabbitai pauseto pause the reviews on a PR.@coderabbitai resumeto resume the paused reviews.@coderabbitai reviewto trigger an incremental review. This is useful when automatic reviews are disabled for the repository.@coderabbitai full reviewto do a full review from scratch and review all the files again.@coderabbitai summaryto regenerate the summary of the PR.@coderabbitai resolveresolve all the CodeRabbit review comments.@coderabbitai configurationto show the current CodeRabbit configuration for the repository.@coderabbitai helpto get help.
Other keywords and placeholders
- Add
@coderabbitai ignoreanywhere in the PR description to prevent this PR from being reviewed. - Add
@coderabbitai summaryto generate the high-level summary at a specific location in the PR description. - Add
@coderabbitaianywhere in the PR title to generate the title automatically.
CodeRabbit Configuration File (.coderabbit.yaml)
- You can programmatically configure CodeRabbit by adding a
.coderabbit.yamlfile to the root of your repository. - Please see the configuration documentation for more information.
- 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/schema.v2.json
Documentation and Community
- Visit our Documentation for detailed information on how to use CodeRabbit.
- Join our Discord Community to get help, request features, and share feedback.
- Follow us on X/Twitter for updates and announcements.
![coderabbitai[bot] avatar](https://avatars.githubusercontent.com/in/347564?v=4 "Published by a pub.dev verified publisher"){kind=small}
Some questions I already had:
- I connected most of the tables via the payment id (primary key) however does it make sense to also include a foreign key which points to the payment hash or even have 2 foreign keys for tables (or is the lookup cost very low so we can go with 1 and from the payment id just get the payment hash from the
mpp_paymenttable. - I removed the need to have a separate table for
PaymentCreationInfobut put everything intompp_paymentrecord this will probably force use to also change the interface of the control_tower for example:
InitPayment(lntypes.Hash, *channeldb.PaymentCreationInfo) error
I think this is ok and we just include an interface for the channeldb.PaymentCreationInfo type.
) however does it make sense to also include a foreign key which points to the payment hash or even have 2 foreign keys for tables (or is the lookup cost very low so we can go with 1 and from the payment id just get the payment hash from the mpp_payment table.
As in a composite foreign key? That should be fine, though if there's a way we can avoid it in practice to simplify queries or instead add a UNIQUE index, then that might be desirable.
I used Claude to create an mermaid diagram of the schema so far (zero shot, and first pass, might have some errors). This is nice, as we can check it into the repo, and the markdown updates will be shown in the git diff:
erDiagram
mpp_payment ||--o{ first_hop_custom_records : has
mpp_payment ||--|| payment_status_types : has
mpp_payment ||--|| mpp_state : has
mpp_payment ||--o{ htlc_attempt : has
htlc_attempt ||--o| htlc_settle_info : has
htlc_attempt ||--o| htlc_fail_info : has
htlc_fail_info ||--|| htlc_fail_reason_types : has
htlc_fail_info ||--o| failure_msg_types : has
htlc_attempt ||--o| route : has
route ||--o{ hop : contains
hop ||--o| blinded_data : has
hop ||--o| mpp_record : has
hop ||--o| amp_record : has
hop ||--o{ hop_custom_records : has
mpp_payment {
integer id PK
integer payment_status FK
blob payment_hash
bigint amount_msat
blob payment_request
timestamp created_at
}
first_hop_custom_records {
bigint key FK
blob value
text payment_id
}
payment_status_types {
integer id PK
text description
}
mpp_state {
integer payment_id FK
integer num_attempts_in_flight
bigint remaining_amt
bigint FeesPaid
boolean has_settled_htlc
boolean payment_failed
}
htlc_attempt {
integer id PK,FK
blob session_key
timestamp attempt_time
integer payment_id FK
}
htlc_settle_info {
integer id PK
integer attempt_id FK
blob preimage
timestamp settle_time
}
htlc_fail_info {
integer id PK
integer attempt_id FK
integer failure_source_index
integer htlc_fail_reason FK
integer failure_msg FK
}
htlc_fail_reason_types {
integer id PK
text description
}
failure_msg_types {
integer id PK
text description
text error_msg
}
route {
integer id PK
integer htlc_attempt_id FK
integer total_timeLock
bigint total_amount
blob source_key
bigint first_hop_amount
}
hop {
integer id PK
integer route_id FK
blob pub_key
text chan_id
integer outgoing_time_lock
bigint amt_to_forward
blob meta_data
}
blinded_data {
integer hop_id FK
blob encrypted_data
blob blinding_point
bigint total_amt
}
mpp_record {
integer hop_id FK
blob payment_addr
bigint total_msat
}
amp_record {
integer hop_id FK
blob root_share
blob set_id
integer child_index
}
hop_custom_records {
bigint key FK
blob value
integer hop_id FK
}
Updated version:
erDiagram
payments {
BIGINT id PK
INTEGER payment_status FK
INTEGER payment_type FK
BIGINT amount_msat
BLOB payment_request
TIMESTAMP created_at
}
payment_status_types {
INTEGER id PK
TEXT description
}
payment_types {
INTEGER id PK
TEXT description
}
legacy_payments {
INTEGER payment_id PK,FK
BLOB payment_hash
}
mpp_payments {
INTEGER payment_id PK,FK
INTEGER mpp_record_id FK
}
amp_payments {
INTEGER payment_id PK,FK
INTEGER amp_record_id FK
}
mpp_state {
INTEGER payment_id PK,FK
INTEGER num_attempts_in_flight
BIGINT remaining_amt
BIGINT FeesPaid
BOOLEAN has_settled_htlc
BOOLEAN payment_failed
}
first_hop_custom_records {
BIGINT key
BLOB value
INTEGER payment_id FK
}
htlc_attempt {
INTEGER id PK
BLOB session_key
TIMESTAMP attempt_time
INTEGER payment_id FK
}
route {
INTEGER htlc_attempt_id PK,FK
INTEGER total_timeLock
BIGINT total_amount
BLOB source_key
BIGINT first_hop_amount
}
hop {
INTEGER id PK
INTEGER route_id FK
BLOB pub_key
TEXT chan_id
INTEGER outgoing_time_lock
BIGINT amt_to_forward
BLOB meta_data
}
mpp_record {
INTEGER hop_id PK,FK
BLOB payment_addr
BIGINT total_msat
}
amp_record {
INTEGER hop_id PK,FK
BLOB root_share
BLOB set_id
INTEGER child_index
}
hop_custom_records {
BIGINT key
BLOB value
BIGINT hop_id FK
}
blinded_data {
INTEGER hop_id PK,FK
BLOB encrypted_data
BLOB blinding_point
BIGINT total_amt
}
htlc_settle_info {
INTEGER htlc_attempt_id PK,FK
BLOB preimage
TIMESTAMP settle_time
}
htlc_fail_info {
INTEGER htlc_attempt_id PK,FK
INTEGER htlc_fail_reason FK
TEXT failure_msg
}
htlc_fail_reason_types {
INTEGER id PK
TEXT description
}
payments ||--o{ legacy_payments : has
payments ||--o{ mpp_payments : has
payments ||--o{ amp_payments : has
payments ||--o{ mpp_state : has
payments ||--o{ first_hop_custom_records : has
payments ||--o{ htlc_attempt : has
payments }|--|| payment_status_types : references
payments }|--|| payment_types : references
mpp_payments }|--|| mpp_record : references
amp_payments }|--|| amp_record : references
htlc_attempt ||--o| route : has
route ||--o{ hop : contains
hop ||--o| mpp_record : has
hop ||--o| amp_record : has
hop ||--o{ hop_custom_records : has
hop ||--o| blinded_data : has
htlc_attempt ||--o| htlc_settle_info : has
htlc_attempt ||--o| htlc_fail_info : has
htlc_fail_info }|--|| htlc_fail_reason_types : references
Updated graph:
erDiagram
payments {
BIGINT id PK
INTEGER payment_status FK
INTEGER payment_type FK
BIGINT amount_msat
TIMESTAMP created_at
}
payment_requests {
INTEGER id PK
INTEGER payment_id FK
BLOB payment_request
}
payment_status_types {
INTEGER id PK
TEXT description
}
payment_types {
INTEGER id PK
TEXT description
}
legacy_payments {
INTEGER payment_id PK,FK
BLOB payment_hash
}
mpp_payments {
INTEGER payment_id PK,FK
INTEGER mpp_record_id FK
}
amp_payments {
INTEGER payment_id PK,FK
INTEGER amp_record_id FK
}
payment_state {
INTEGER payment_id PK,FK
INTEGER num_attempts_in_flight
BIGINT remaining_amt
BIGINT fees_paid
BOOLEAN has_settled_htlc
BOOLEAN payment_failed
}
htlc_attempt {
INTEGER id PK
BLOB session_key
TIMESTAMP attempt_time
INTEGER payment_id FK
}
route {
INTEGER htlc_attempt_id PK,FK
INTEGER total_timeLock
BIGINT total_amount
BLOB source_key
BIGINT first_hop_amount
}
hop {
INTEGER id PK
INTEGER route_id FK
BLOB pub_key
TEXT chan_id
INTEGER outgoing_time_lock
BIGINT amt_to_forward
BLOB meta_data
}
mpp_record {
INTEGER hop_id PK,FK
BLOB payment_addr
BIGINT total_msat
}
amp_record {
INTEGER hop_id PK,FK
BLOB root_share
BLOB set_id
INTEGER child_index
}
tlv_records {
INTEGER id PK
BIGINT key
BLOB value
}
custom_records {
INTEGER tlv_record_id PK,FK
INTEGER hop_id FK
}
first_hop_custom_records {
INTEGER tlv_record_id PK,FK
INTEGER payment_id FK
}
blinded_data {
INTEGER hop_id PK,FK
BLOB encrypted_data
BLOB blinding_point
BIGINT total_amt
}
htlc_settle_info {
INTEGER htlc_attempt_id PK,FK
BLOB preimage
TIMESTAMP settle_time
}
htlc_fail_info {
INTEGER htlc_attempt_id PK,FK
INTEGER htlc_fail_reason FK
TEXT failure_msg
}
htlc_fail_reason_types {
INTEGER id PK
TEXT description
}
payments ||--o{ payment_requests : has
payments ||--o| legacy_payments : has
payments ||--o| mpp_payments : has
payments ||--o| amp_payments : has
payments ||--o| payment_state : has
payments ||--o{ htlc_attempt : has
payments }o--|| payment_status_types : has
payments }o--|| payment_types : has
htlc_attempt ||--o| route : has
route ||--o{ hop : has
hop ||--o| mpp_record : has
hop ||--o| amp_record : has
hop ||--o{ custom_records : has
hop ||--o| blinded_data : has
htlc_attempt ||--o| htlc_settle_info : has
htlc_attempt ||--o| htlc_fail_info : has
htlc_fail_info }o--|| htlc_fail_reason_types : has
mpp_record ||--|| mpp_payments : references
amp_record ||--|| amp_payments : references
tlv_records ||--o{ custom_records : has
tlv_records ||--o{ first_hop_custom_records : has
payments ||--o{ first_hop_custom_records : has
Currently creating the whole change into the one draft PR and then if it all works together will split all the different parts into separate PRs.
List of ideas to include during refactor (in progress):
- garbage collector for failed payments on startup
fix the nil hash patch properly via SQL: https://github.com/lightningnetwork/lnd/pull/9703#issuecomment-2801670538
New updated Diagram: https://dbdiagram.io/d/Payment-Schema-66fbb44df9b1444815012417
DB design changed quite a bit:
erDiagram
payments {
integer id PK
blob payment_request
bigint amount_msat
timestamp created_at
blob payment_hash UK "UNIQUE"
integer fail_reason
}
payment_htlc_attempts {
integer id PK
bigint attempt_index UK "UNIQUE"
bigint payment_id FK
blob session_key UK "UNIQUE"
timestamp attempt_time
blob payment_hash
bigint first_hop_amount_msat
integer route_total_time_lock
bigint route_total_amount
blob route_source_key
integer failure_source_index
integer htlc_fail_reason
blob failure_msg
timestamp fail_time
blob settle_preimage
timestamp settle_time
}
payment_route_hops {
integer id PK
bigint htlc_attempt_index FK
integer hop_index
blob pub_key
text scid
integer outgoing_time_lock
bigint amt_to_forward
blob meta_data
boolean legacy_payload
blob mpp_payment_addr
bigint mpp_total_msat
blob amp_root_share
blob amp_set_id
integer amp_child_index
blob encrypted_data
blob blinding_point
bigint blinded_path_total_amt
}
payment_first_hop_custom_records {
integer id PK
bigint key
blob value
bigint payment_id FK
}
payment_htlc_attempt_custom_records {
integer id PK
bigint key
blob value
bigint htlc_attempt_index FK
}
payment_route_hop_custom_records {
integer id PK
bigint key
blob value
bigint hop_id FK
}
%% Relationships
payments ||--o{ payment_htlc_attempts : "has many"
payments ||--o{ payment_first_hop_custom_records : "has many"
payment_htlc_attempts ||--o{ payment_route_hops : "has many"
payment_htlc_attempts ||--o{ payment_htlc_attempt_custom_records : "has many"
payment_route_hops ||--o{ payment_route_hop_custom_records : "has many"
Probably we can move all the amp,mpp,blinded data fields into a separate table and link it from the hop table, because for example most of the stuff is only part of the last hop (for AMP,MPP) so we avoid carrying all this NULL fields in every hop for every payment ?
Acknowledged. Initiating a review of the pull request. Please allow some time for the analysis to complete.
![gemini-code-assist[bot] avatar](https://avatars.githubusercontent.com/in/956858?v=4 "Published by a pub.dev verified publisher"){kind=small}
I created a refined schema which is more optimzed:
https://dbdiagram.io/d/Payment-Schema-66fbb44df9b1444815012417
OK so I broke the whole PR into pieces and will use a side branch instead to merge those PRs until the whole feature is ready. The tests which use the nativesql-experiment will fail currently because the backend is not implemented yet.
Couple of words regarding the new schema and the design choices:
Differences to the old design:
-
Removed the Legacy_Payload bit because it is not necessary, will only use the tlv payload and if we still have some old payload which uses the legacy payload format we will deserialize it and move it into the proper table, so there is no need flagging legacy payload.
-
Moved the payment request into its own table to account for Bolt12 offers which will have a offer instead of and invoice we would initiate the payment to.
-
Also decouple the resolution from the attempt table to have them properly separated. I decided against splitting the settle and fail information into its own tables because then we would not have an easy way to check for consitency meaning that either the failure reason for this attempt or the settle info is set.
-
Now a short word to the CustomHop data, we have custom hop data on 3 levels: the payment level (which carries the custom tlv data for the first hop), the attempt/route level tlv custom payload (which is very similar to the payment level tlv custom record payload, but for the custom channels we add additional information via the AuxUnit so we have to persist it as well) and the hop level custom record payload which has the tlv custom record data for all other hops except the first hop. I dedided to keep them separate for now, because having a centralized TLV table and only link tables from the payment,route,hop might save us some data but comes with more complexity.
-
We separated the MPP, AMP and BLinded information into its own tables, because for the MPP and AMP case it only effects the last hop so we would have a lot of waste stored if we integrated it into the. hops table. For the blinded data I also separated it out, because most payment will not have it. and a small join will not effect much the speed in fetching the blinded data.
-
Also what is important to note: The extra attemptIndex in the attempts table will be removed later down the road before the feature lands into master, as soon as the circuit map dependency is removed, this will happen after the basic SQL functionality is merged into the side branch. It does not hurt much currently and it is a bit of a refactor to remove the dependency to the switch to that we can just use the primary key of the attempt.
@yyforyongyu: review reminder @ziggie1984, remember to re-request review from reviewers when ready
2025-10-13 07:07:15.700 [DBG] CRTR router.go:1419: Scanning for inflight payments panic: runtime error: invalid memory address or nil pointer dereference [signal SIGSEGV: segmentation violation code=0x1 addr=0x48 pc=0x1bed372]
yes that's expected, we embed the kvstore for the sqlstore for now so that we can compile it, but for the sql tests they will fail because I never initialize this value because it would not work anyways if we partly implement each function because then parts will live in the kvstore and parts in the sql store, thats why for now these itests will fail for the test_native_sql run.