Remove four indexes from the invoices table that either duplicate existing
UNIQUE constraint indexes or are never used in WHERE clauses:
- invoices_hash_idx: redundant, UNIQUE constraint on hash already creates
an implicit index
- invoices_payment_addr_idx: redundant, UNIQUE constraint on payment_addr
already creates an implicit index
- invoices_preimage_idx: unused, preimage is NULL on all new invoices and
is never used as a query filter
- invoices_settled_at_idx: unused, settled_at is NULL on all pending
invoices and is never used as a query filter (settle_index is used
instead)
Dropping these reduces B-tree working set size, which improves page cache
utilization as the invoice table grows.
Older LND versions could create multiple payments for the same hash.
We need to preserve those historical records during KV→SQL migration,
but they don’t fit the normal payment schema because we enforce a
unique payment hash constraint. Introduce a lean payment_duplicates
table to store only the essential fields (identifier, amount,
timestamps, settle/fail data).
This keeps the primary payment records stable and makes the migration
deterministic even when duplicate records lack attempt info. The table
is intentionally minimal and can be dropped after migration if no
duplicate payments exist.
For now there is no logic in place which allows the noderunner to
fetch duplicate payments after the migration.
- Remove duplicate compile-time interface assertion for SQLStore.
- Fix the down migration to drop payment_intents before payments to
respect the foreign key dependency order. This was not a bug in the
first place bc we have the CASCADE when deleting payments.
The 000009 schema version slot is now taken by 000009_graph_v2_columns
which was merged ahead of the payments schema. Bump the payments schema
file number to 000010 to avoid the collision.
Moreover update the migration_dev.go file to reflect this change and
update the order of migration.
Previously a one(intent)-to-many(payment) relationship it is now
changed to a one-to-one relationship because a payment request
only can have 1 payment related to it. Looking into the future
with BOLT12 offers, the fetched invoice from the offer could be
stored here as well and the relationship would still hold.
Add a new migration that updates the graph tables (nodes, channels and
policies) in preparation for the new columns required for V2
announcements. This migration has to be added to the set of "live"
migrations instead of "dev only" since it edits the columns of existing
tables and so changes the existing sql models. We are going to prep the
SQLStore code to handle the V2 types in the coming commits, so we need
this migration to be in place.
In this commit we also remove the TestSchemaMigrationIdempotency test
since this test fails with the new "ALTER TABLE" migrations which dont
have "IF NOT EXISTS" options like tables and indexes do. Migrations
should be idempotent anyways due to the migration tracker file and/or
the sqlc migration tracker.
We put this new schema update into the main line and change the
versions of the schema updates which are currently only available
in dev builds. The schemas need to be chronological therefore we
also need to rename the file numbers.
We need to explicitly store the entire bitfield types since we may have
channel_updates with bitfields containing bits we just dont need or
understand but we still need to store the entire bitfield so that the
reconstructed announcement remains valid.
This commit only adds the new columns but does not use them yet. NOTE:
this is ok since the migration adding this schema is not available in
the production build yet.
Note that this table will only contain entries for channels that we have
deleted from the `channels` table which is why we cannot use foreign
keys. Similarly, we may no longer have node entries for the nodes in the
table.
In this commit, we define the SQL schemas for storing graph channel
data. This includes a new `channels` table and a new `channel_features`
table along with various indices.
In this commit, we add the `source_nodes` table. It points to entries in
the `nodes` table. This table will store one entry per protocol version
that we are announcing a node_announcement on.
With this commit, we can run the TestSourceNode unit test against our
SQL backends.
In this commit, the various SQL schemas required to store graph node
related data is defined. Specifically, the following tables are defined:
- nodes
- node_extra_types
- node_features
- node_addresses
Previously, we applied replacements to our schema definitions
to make them compatible with both SQLite and Postgres backends,
as the files were not fully compatible with either.
With this change, the only replacement required for SQLite has
been moved to the generator script. This adjustment ensures
compatibility by enabling auto-incrementing primary keys that
are treated as 64-bit integers by sqlc.
Previously, when using the native schema, invoice expiries were incorrectly
stored as 64-bit values (expiry in nanoseconds instead of seconds), causing
overflow issues. Since we cannot determine the original values, we will set
the expiries for existing invoices to 1 hour with this migration.
This commit attempts to fix some issues with the invoice store's schema that we
couldn't foresee before the implementation was finished. This is safe as the
schema has not been instantiated yet outside of unit tests. Furthermore the
commit updates invoice store SQL queries according to fixes in the schema as
well as to prepare the higher level implementation in the upcoming commits.
Schema for AMP invocies.
AMP invoices can be paid multiple times and each payment to an AMP invoice
is identified by a `set_id`.
The A in AMP stands for `Atomic`. All the htlcs belonging to the same
AMP payment (share the same set_id) will be resolved at the same time
with the same result: settled/canceled.
AMP invoices do not have an "invoice preimage". Instead, each htcl has
its own hash/preimage. When a new htlc is added the hash for that htlc
is attached to it. When all the htlcs of a set_id have been received we
are able to compute the preimage for each one of them.
This is the schema for "ordinal" BOLT11 invoices.
The invoices table aims to keep an entry for each invoice, BOLT11 or not,
that will be supported.
Invoice related HTLCs will be stored in a separete table than forwarded
htlcs.
SQLite does not support `SEQUENCE`. We achieve atomic autoincrementals
using primary keys with autoincrement/serial. An invoice `AddIndex`
translates to `invoices(id)` while `SettleIndex` is `invoice_payments(id)`.
ziggie