← Watch feed
sqldb: add new gossip v2 columns to graph tables
Public commit record
90/100 · Strong
What the developer wrote
Authored by Elle Mouton
sqldb: add new gossip v2 columns to graph tables
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.
✓ Descriptive subject✓ Names a concrete action or component✓ Provides detailed explanatory context✓ Explains rationale or failure mode✓ Mentions testing or verification
What changed, and why it matters
This commit is a routine database schema update for an upcoming Lightning Network feature called "gossip v2." It adds new empty columns to tables that store node, channel, and routing-policy data, and updates the code that reads those tables to expect the new columns. It also removes an automated test that checked whether schema migrations could be safely run over and over, because the new migration uses SQL "ALTER TABLE" commands that are not naturally repeatable. There is no direct security vulnerability visible in the diff, but removing the idempotency test slightly weakens the project's safety net against migration mistakes.
Recommended action
Treat as a normal feature-prep commit. Re-add a targeted idempotency test for migrations that do support it, or add explicit guards (e.g., catalog checks) so ALTER TABLE migrations fail safely if re-applied. Monitor the upcoming V2 gossip implementation commits for proper validation of signature, funding_pk_script, and merkle_root_hash fields.
Security signals we found
01
Removal of an idempotency test for schema migrations
02
New nullable columns added to existing graph tables via ALTER TABLE
03
No input validation, signature verification, or authorization logic changed
04
No CVE, advisory, or vendor security disclosure referenced in commit
Evidence from the diff
The change adds migration 000009_graph_v2.up.sql, which uses ALTER TABLE to add nullable columns: graph_nodes.block_height, graph_channels.signature/funding_pk_script/merkle_root_hash, and graph_channel_policies.block_height/disable_flags. Corresponding Go structs and sqlc-generated SELECT queries are updated to include the new fields. A down-migration drops the columns. The TestSchemaMigrationIdempotency test is deleted because ALTER TABLE ADD/DROP COLUMN statements are not idempotent in the way CREATE TABLE IF NOT EXISTS is; the commit message argues idempotency is still ensured by the migration tracker. No logic that writes, validates, or interprets these new fields is introduced here.
Changed components
sqldb/migrations.gosqldb/migrations_test.gosqldb/sqlc/graph.sql.gosqldb/sqlc/migrations/000009_graph_v2.up.sqlsqldb/sqlc/migrations/000009_graph_v2.down.sqlsqldb/sqlc/models.goInspect captured patch +140 / −158
diff --git a/sqldb/migrations.go b/sqldb/migrations.go
index 38535d8..28d45f3 100644
--- a/sqldb/migrations.go
+++ b/sqldb/migrations.go
@@ -92,6 +92,11 @@ var (
// schema. This is optional and can be disabled by the
// user if necessary.
},
+ {
+ Name: "000009_graph_v2_columns",
+ Version: 11,
+ SchemaVersion: 9,
+ },
}, migrationAdditions...)
// ErrMigrationMismatch is returned when a migrated record does not
diff --git a/sqldb/migrations_test.go b/sqldb/migrations_test.go
index 063f810..536ba89 100644
--- a/sqldb/migrations_test.go
+++ b/sqldb/migrations_test.go
@@ -452,132 +452,6 @@ func TestCustomMigration(t *testing.T) {
}
}
-// TestSchemaMigrationIdempotency tests that the our schema migrations are
-// idempotent. This means that we can apply the migrations multiple times and
-// the schema version will always be the same.
-func TestSchemaMigrationIdempotency(t *testing.T) {
- dropMigrationTrackerEntries := func(t *testing.T, db *BaseDB) {
- _, err := db.Exec("DELETE FROM migration_tracker;")
- require.NoError(t, err)
- }
-
- lastMigration := migrationConfig[len(migrationConfig)-1]
-
- t.Run("SQLite", func(t *testing.T) {
- // First instantiate the database and run the migrations
- // including the custom migrations.
- t.Logf("Creating new SQLite DB for testing migrations")
-
- dbFileName := filepath.Join(t.TempDir(), "tmp.db")
- var (
- db *SqliteStore
- err error
- )
-
- // Run the migration 3 times to test that the migrations
- // are idempotent.
- for i := 0; i < 3; i++ {
- db, err = NewSqliteStore(&SqliteConfig{
- SkipMigrations: false,
- }, dbFileName)
- require.NoError(t, err)
-
- dbToCleanup := db.DB
- t.Cleanup(func() {
- require.NoError(
- t, dbToCleanup.Close(),
- )
- })
-
- ctxb := t.Context()
- require.NoError(
- t, db.ApplyAllMigrations(ctxb, GetMigrations()),
- )
-
- version, dirty, err := db.GetSchemaVersion()
- require.NoError(t, err)
-
- // Now reset the schema version to 0 and make sure that
- // we can apply the migrations again.
- require.Equal(t, lastMigration.SchemaVersion, version)
- require.False(t, dirty)
-
- require.NoError(
- t, db.SetSchemaVersion(
- database.NilVersion, false,
- ),
- )
- dropMigrationTrackerEntries(t, db.BaseDB)
-
- // Make sure that we reset the schema version.
- version, dirty, err = db.GetSchemaVersion()
- require.NoError(t, err)
- require.Equal(t, -1, version)
- require.False(t, dirty)
- }
- })
-
- t.Run("Postgres", func(t *testing.T) {
- // First create a temporary Postgres database to run
- // the migrations on.
- fixture := NewTestPgFixture(
- t, DefaultPostgresFixtureLifetime,
- )
- t.Cleanup(func() {
- fixture.TearDown(t)
- })
-
- dbName := randomDBName(t)
-
- // Next instantiate the database and run the migrations
- // including the custom migrations.
- t.Logf("Creating new Postgres DB '%s' for testing "+
- "migrations", dbName)
-
- _, err := fixture.db.ExecContext(
- t.Context(), "CREATE DATABASE "+dbName,
- )
- require.NoError(t, err)
-
- cfg := fixture.GetConfig(dbName)
- var db *PostgresStore
-
- // Run the migration 3 times to test that the migrations
- // are idempotent.
- for i := 0; i < 3; i++ {
- cfg.SkipMigrations = false
- db, err = NewPostgresStore(cfg)
- require.NoError(t, err)
-
- ctxb := t.Context()
- require.NoError(
- t, db.ApplyAllMigrations(ctxb, GetMigrations()),
- )
-
- version, dirty, err := db.GetSchemaVersion()
- require.NoError(t, err)
-
- // Now reset the schema version to 0 and make sure that
- // we can apply the migrations again.
- require.Equal(t, lastMigration.SchemaVersion, version)
- require.False(t, dirty)
-
- require.NoError(
- t, db.SetSchemaVersion(
- database.NilVersion, false,
- ),
- )
- dropMigrationTrackerEntries(t, db.BaseDB)
-
- // Make sure that we reset the schema version.
- version, dirty, err = db.GetSchemaVersion()
- require.NoError(t, err)
- require.Equal(t, -1, version)
- require.False(t, dirty)
- }
- })
-}
-
// TestMigrationBug19RC1 tests a bug that was present in the migration code
// at the v0.19.0-rc1 release.
// The bug was fixed in: https://github.com/lightningnetwork/lnd/pull/9647
diff --git a/sqldb/sqlc/graph.sql.go b/sqldb/sqlc/graph.sql.go
index 0ce7780..8b10e49 100644
--- a/sqldb/sqlc/graph.sql.go
+++ b/sqldb/sqlc/graph.sql.go
@@ -290,7 +290,7 @@ func (q *Queries) DeleteZombieChannel(ctx context.Context, arg DeleteZombieChann
const getChannelAndNodesBySCID = `-- name: GetChannelAndNodesBySCID :one
SELECT
- c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature,
+ c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature, c.signature, c.funding_pk_script, c.merkle_root_hash,
n1.pub_key AS node1_pub_key,
n2.pub_key AS node2_pub_key
FROM graph_channels c
@@ -319,6 +319,9 @@ type GetChannelAndNodesBySCIDRow struct {
Node2Signature []byte
Bitcoin1Signature []byte
Bitcoin2Signature []byte
+ Signature []byte
+ FundingPkScript []byte
+ MerkleRootHash []byte
Node1PubKey []byte
Node2PubKey []byte
}
@@ -340,6 +343,9 @@ func (q *Queries) GetChannelAndNodesBySCID(ctx context.Context, arg GetChannelAn
&i.Node2Signature,
&i.Bitcoin1Signature,
&i.Bitcoin2Signature,
+ &i.Signature,
+ &i.FundingPkScript,
+ &i.MerkleRootHash,
&i.Node1PubKey,
&i.Node2PubKey,
)
@@ -348,7 +354,7 @@ func (q *Queries) GetChannelAndNodesBySCID(ctx context.Context, arg GetChannelAn
const getChannelByOutpointWithPolicies = `-- name: GetChannelByOutpointWithPolicies :one
SELECT
- c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature,
+ c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature, c.signature, c.funding_pk_script, c.merkle_root_hash,
n1.pub_key AS node1_pubkey,
n2.pub_key AS node2_pubkey,
@@ -454,6 +460,9 @@ func (q *Queries) GetChannelByOutpointWithPolicies(ctx context.Context, arg GetC
&i.GraphChannel.Node2Signature,
&i.GraphChannel.Bitcoin1Signature,
&i.GraphChannel.Bitcoin2Signature,
+ &i.GraphChannel.Signature,
+ &i.GraphChannel.FundingPkScript,
+ &i.GraphChannel.MerkleRootHash,
&i.Node1Pubkey,
&i.Node2Pubkey,
&i.Policy1ID,
@@ -491,7 +500,7 @@ func (q *Queries) GetChannelByOutpointWithPolicies(ctx context.Context, arg GetC
}
const getChannelBySCID = `-- name: GetChannelBySCID :one
-SELECT id, version, scid, node_id_1, node_id_2, outpoint, capacity, bitcoin_key_1, bitcoin_key_2, node_1_signature, node_2_signature, bitcoin_1_signature, bitcoin_2_signature FROM graph_channels
+SELECT id, version, scid, node_id_1, node_id_2, outpoint, capacity, bitcoin_key_1, bitcoin_key_2, node_1_signature, node_2_signature, bitcoin_1_signature, bitcoin_2_signature, signature, funding_pk_script, merkle_root_hash FROM graph_channels
WHERE scid = $1 AND version = $2
`
@@ -517,15 +526,18 @@ func (q *Queries) GetChannelBySCID(ctx context.Context, arg GetChannelBySCIDPara
&i.Node2Signature,
&i.Bitcoin1Signature,
&i.Bitcoin2Signature,
+ &i.Signature,
+ &i.FundingPkScript,
+ &i.MerkleRootHash,
)
return i, err
}
const getChannelBySCIDWithPolicies = `-- name: GetChannelBySCIDWithPolicies :one
SELECT
- c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature,
- n1.id, n1.version, n1.pub_key, n1.alias, n1.last_update, n1.color, n1.signature,
- n2.id, n2.version, n2.pub_key, n2.alias, n2.last_update, n2.color, n2.signature,
+ c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature, c.signature, c.funding_pk_script, c.merkle_root_hash,
+ n1.id, n1.version, n1.pub_key, n1.alias, n1.last_update, n1.color, n1.signature, n1.block_height,
+ n2.id, n2.version, n2.pub_key, n2.alias, n2.last_update, n2.color, n2.signature, n2.block_height,
-- Policy 1
cp1.id AS policy1_id,
@@ -630,6 +642,9 @@ func (q *Queries) GetChannelBySCIDWithPolicies(ctx context.Context, arg GetChann
&i.GraphChannel.Node2Signature,
&i.GraphChannel.Bitcoin1Signature,
&i.GraphChannel.Bitcoin2Signature,
+ &i.GraphChannel.Signature,
+ &i.GraphChannel.FundingPkScript,
+ &i.GraphChannel.MerkleRootHash,
&i.GraphNode.ID,
&i.GraphNode.Version,
&i.GraphNode.PubKey,
@@ -637,6 +652,7 @@ func (q *Queries) GetChannelBySCIDWithPolicies(ctx context.Context, arg GetChann
&i.GraphNode.LastUpdate,
&i.GraphNode.Color,
&i.GraphNode.Signature,
+ &i.GraphNode.BlockHeight,
&i.GraphNode_2.ID,
&i.GraphNode_2.Version,
&i.GraphNode_2.PubKey,
@@ -644,6 +660,7 @@ func (q *Queries) GetChannelBySCIDWithPolicies(ctx context.Context, arg GetChann
&i.GraphNode_2.LastUpdate,
&i.GraphNode_2.Color,
&i.GraphNode_2.Signature,
+ &i.GraphNode_2.BlockHeight,
&i.Policy1ID,
&i.Policy1NodeID,
&i.Policy1Version,
@@ -764,7 +781,7 @@ func (q *Queries) GetChannelFeaturesBatch(ctx context.Context, chanIds []int64)
}
const getChannelPolicyByChannelAndNode = `-- name: GetChannelPolicyByChannelAndNode :one
-SELECT id, version, channel_id, node_id, timelock, fee_ppm, base_fee_msat, min_htlc_msat, max_htlc_msat, last_update, disabled, inbound_base_fee_msat, inbound_fee_rate_milli_msat, message_flags, channel_flags, signature
+SELECT id, version, channel_id, node_id, timelock, fee_ppm, base_fee_msat, min_htlc_msat, max_htlc_msat, last_update, disabled, inbound_base_fee_msat, inbound_fee_rate_milli_msat, message_flags, channel_flags, signature, block_height, disable_flags
FROM graph_channel_policies
WHERE channel_id = $1
AND node_id = $2
@@ -797,6 +814,8 @@ func (q *Queries) GetChannelPolicyByChannelAndNode(ctx context.Context, arg GetC
&i.MessageFlags,
&i.ChannelFlags,
&i.Signature,
+ &i.BlockHeight,
+ &i.DisableFlags,
)
return i, err
}
@@ -852,7 +871,7 @@ func (q *Queries) GetChannelPolicyExtraTypesBatch(ctx context.Context, policyIds
const getChannelsByIDs = `-- name: GetChannelsByIDs :many
SELECT
- c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature,
+ c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature, c.signature, c.funding_pk_script, c.merkle_root_hash,
-- Minimal node data.
n1.id AS node1_id,
@@ -975,6 +994,9 @@ func (q *Queries) GetChannelsByIDs(ctx context.Context, ids []int64) ([]GetChann
&i.GraphChannel.Node2Signature,
&i.GraphChannel.Bitcoin1Signature,
&i.GraphChannel.Bitcoin2Signature,
+ &i.GraphChannel.Signature,
+ &i.GraphChannel.FundingPkScript,
+ &i.GraphChannel.MerkleRootHash,
&i.Node1ID,
&i.Node1PubKey,
&i.Node2ID,
@@ -1025,7 +1047,7 @@ func (q *Queries) GetChannelsByIDs(ctx context.Context, ids []int64) ([]GetChann
const getChannelsByOutpoints = `-- name: GetChannelsByOutpoints :many
SELECT
- c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature,
+ c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature, c.signature, c.funding_pk_script, c.merkle_root_hash,
n1.pub_key AS node1_pubkey,
n2.pub_key AS node2_pubkey
FROM graph_channels c
@@ -1074,6 +1096,9 @@ func (q *Queries) GetChannelsByOutpoints(ctx context.Context, outpoints []string
&i.GraphChannel.Node2Signature,
&i.GraphChannel.Bitcoin1Signature,
&i.GraphChannel.Bitcoin2Signature,
+ &i.GraphChannel.Signature,
+ &i.GraphChannel.FundingPkScript,
+ &i.GraphChannel.MerkleRootHash,
&i.Node1Pubkey,
&i.Node2Pubkey,
); err != nil {
@@ -1092,9 +1117,9 @@ func (q *Queries) GetChannelsByOutpoints(ctx context.Context, outpoints []string
const getChannelsByPolicyLastUpdateRange = `-- name: GetChannelsByPolicyLastUpdateRange :many
SELECT
- c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature,
- n1.id, n1.version, n1.pub_key, n1.alias, n1.last_update, n1.color, n1.signature,
- n2.id, n2.version, n2.pub_key, n2.alias, n2.last_update, n2.color, n2.signature,
+ c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature, c.signature, c.funding_pk_script, c.merkle_root_hash,
+ n1.id, n1.version, n1.pub_key, n1.alias, n1.last_update, n1.color, n1.signature, n1.block_height,
+ n2.id, n2.version, n2.pub_key, n2.alias, n2.last_update, n2.color, n2.signature, n2.block_height,
-- Policy 1 (node_id_1)
cp1.id AS policy1_id,
@@ -1244,6 +1269,9 @@ func (q *Queries) GetChannelsByPolicyLastUpdateRange(ctx context.Context, arg Ge
&i.GraphChannel.Node2Signature,
&i.GraphChannel.Bitcoin1Signature,
&i.GraphChannel.Bitcoin2Signature,
+ &i.GraphChannel.Signature,
+ &i.GraphChannel.FundingPkScript,
+ &i.GraphChannel.MerkleRootHash,
&i.GraphNode.ID,
&i.GraphNode.Version,
&i.GraphNode.PubKey,
@@ -1251,6 +1279,7 @@ func (q *Queries) GetChannelsByPolicyLastUpdateRange(ctx context.Context, arg Ge
&i.GraphNode.LastUpdate,
&i.GraphNode.Color,
&i.GraphNode.Signature,
+ &i.GraphNode.BlockHeight,
&i.GraphNode_2.ID,
&i.GraphNode_2.Version,
&i.GraphNode_2.PubKey,
@@ -1258,6 +1287,7 @@ func (q *Queries) GetChannelsByPolicyLastUpdateRange(ctx context.Context, arg Ge
&i.GraphNode_2.LastUpdate,
&i.GraphNode_2.Color,
&i.GraphNode_2.Signature,
+ &i.GraphNode_2.BlockHeight,
&i.Policy1ID,
&i.Policy1NodeID,
&i.Policy1Version,
@@ -1303,7 +1333,7 @@ func (q *Queries) GetChannelsByPolicyLastUpdateRange(ctx context.Context, arg Ge
}
const getChannelsBySCIDRange = `-- name: GetChannelsBySCIDRange :many
-SELECT c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature,
+SELECT c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature, c.signature, c.funding_pk_script, c.merkle_root_hash,
n1.pub_key AS node1_pub_key,
n2.pub_key AS node2_pub_key
FROM graph_channels c
@@ -1347,6 +1377,9 @@ func (q *Queries) GetChannelsBySCIDRange(ctx context.Context, arg GetChannelsByS
&i.GraphChannel.Node2Signature,
&i.GraphChannel.Bitcoin1Signature,
&i.GraphChannel.Bitcoin2Signature,
+ &i.GraphChannel.Signature,
+ &i.GraphChannel.FundingPkScript,
+ &i.GraphChannel.MerkleRootHash,
&i.Node1PubKey,
&i.Node2PubKey,
); err != nil {
@@ -1365,9 +1398,9 @@ func (q *Queries) GetChannelsBySCIDRange(ctx context.Context, arg GetChannelsByS
const getChannelsBySCIDWithPolicies = `-- name: GetChannelsBySCIDWithPolicies :many
SELECT
- c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature,
- n1.id, n1.version, n1.pub_key, n1.alias, n1.last_update, n1.color, n1.signature,
- n2.id, n2.version, n2.pub_key, n2.alias, n2.last_update, n2.color, n2.signature,
+ c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature, c.signature, c.funding_pk_script, c.merkle_root_hash,
+ n1.id, n1.version, n1.pub_key, n1.alias, n1.last_update, n1.color, n1.signature, n1.block_height,
+ n2.id, n2.version, n2.pub_key, n2.alias, n2.last_update, n2.color, n2.signature, n2.block_height,
-- Policy 1
cp1.id AS policy1_id,
@@ -1490,6 +1523,9 @@ func (q *Queries) GetChannelsBySCIDWithPolicies(ctx context.Context, arg GetChan
&i.GraphChannel.Node2Signature,
&i.GraphChannel.Bitcoin1Signature,
&i.GraphChannel.Bitcoin2Signature,
+ &i.GraphChannel.Signature,
+ &i.GraphChannel.FundingPkScript,
+ &i.GraphChannel.MerkleRootHash,
&i.GraphNode.ID,
&i.GraphNode.Version,
&i.GraphNode.PubKey,
@@ -1497,6 +1533,7 @@ func (q *Queries) GetChannelsBySCIDWithPolicies(ctx context.Context, arg GetChan
&i.GraphNode.LastUpdate,
&i.GraphNode.Color,
&i.GraphNode.Signature,
+ &i.GraphNode.BlockHeight,
&i.GraphNode_2.ID,
&i.GraphNode_2.Version,
&i.GraphNode_2.PubKey,
@@ -1504,6 +1541,7 @@ func (q *Queries) GetChannelsBySCIDWithPolicies(ctx context.Context, arg GetChan
&i.GraphNode_2.LastUpdate,
&i.GraphNode_2.Color,
&i.GraphNode_2.Signature,
+ &i.GraphNode_2.BlockHeight,
&i.Policy1ID,
&i.Policy1NodeID,
&i.Policy1Version,
@@ -1549,7 +1587,7 @@ func (q *Queries) GetChannelsBySCIDWithPolicies(ctx context.Context, arg GetChan
}
const getChannelsBySCIDs = `-- name: GetChannelsBySCIDs :many
-SELECT id, version, scid, node_id_1, node_id_2, outpoint, capacity, bitcoin_key_1, bitcoin_key_2, node_1_signature, node_2_signature, bitcoin_1_signature, bitcoin_2_signature FROM graph_channels
+SELECT id, version, scid, node_id_1, node_id_2, outpoint, capacity, bitcoin_key_1, bitcoin_key_2, node_1_signature, node_2_signature, bitcoin_1_signature, bitcoin_2_signature, signature, funding_pk_script, merkle_root_hash FROM graph_channels
WHERE version = $1
AND scid IN (/*SLICE:scids*/?)
`
@@ -1593,6 +1631,9 @@ func (q *Queries) GetChannelsBySCIDs(ctx context.Context, arg GetChannelsBySCIDs
&i.Node2Signature,
&i.Bitcoin1Signature,
&i.Bitcoin2Signature,
+ &i.Signature,
+ &i.FundingPkScript,
+ &i.MerkleRootHash,
); err != nil {
return nil, err
}
@@ -1756,7 +1797,7 @@ func (q *Queries) GetNodeAddressesBatch(ctx context.Context, ids []int64) ([]Gra
}
const getNodeByPubKey = `-- name: GetNodeByPubKey :one
-SELECT id, version, pub_key, alias, last_update, color, signature
+SELECT id, version, pub_key, alias, last_update, color, signature, block_height
FROM graph_nodes
WHERE pub_key = $1
AND version = $2
@@ -1778,6 +1819,7 @@ func (q *Queries) GetNodeByPubKey(ctx context.Context, arg GetNodeByPubKeyParams
&i.LastUpdate,
&i.Color,
&i.Signature,
+ &i.BlockHeight,
)
return i, err
}
@@ -1947,7 +1989,7 @@ func (q *Queries) GetNodeIDByPubKey(ctx context.Context, arg GetNodeIDByPubKeyPa
}
const getNodesByIDs = `-- name: GetNodesByIDs :many
-SELECT id, version, pub_key, alias, last_update, color, signature
+SELECT id, version, pub_key, alias, last_update, color, signature, block_height
FROM graph_nodes
WHERE id IN (/*SLICE:ids*/?)
`
@@ -1979,6 +2021,7 @@ func (q *Queries) GetNodesByIDs(ctx context.Context, ids []int64) ([]GraphNode,
&i.LastUpdate,
&i.Color,
&i.Signature,
+ &i.BlockHeight,
); err != nil {
return nil, err
}
@@ -1994,7 +2037,7 @@ func (q *Queries) GetNodesByIDs(ctx context.Context, ids []int64) ([]GraphNode,
}
const getNodesByLastUpdateRange = `-- name: GetNodesByLastUpdateRange :many
-SELECT id, version, pub_key, alias, last_update, color, signature
+SELECT id, version, pub_key, alias, last_update, color, signature, block_height
FROM graph_nodes
WHERE last_update >= $1
AND last_update <= $2
@@ -2061,6 +2104,7 @@ func (q *Queries) GetNodesByLastUpdateRange(ctx context.Context, arg GetNodesByL
&i.LastUpdate,
&i.Color,
&i.Signature,
+ &i.BlockHeight,
); err != nil {
return nil, err
}
@@ -2143,7 +2187,7 @@ func (q *Queries) GetPruneTip(ctx context.Context) (GraphPruneLog, error) {
}
const getPublicV1ChannelsBySCID = `-- name: GetPublicV1ChannelsBySCID :many
-SELECT id, version, scid, node_id_1, node_id_2, outpoint, capacity, bitcoin_key_1, bitcoin_key_2, node_1_signature, node_2_signature, bitcoin_1_signature, bitcoin_2_signature
+SELECT id, version, scid, node_id_1, node_id_2, outpoint, capacity, bitcoin_key_1, bitcoin_key_2, node_1_signature, node_2_signature, bitcoin_1_signature, bitcoin_2_signature, signature, funding_pk_script, merkle_root_hash
FROM graph_channels
WHERE node_1_signature IS NOT NULL
AND scid >= $1
@@ -2178,6 +2222,9 @@ func (q *Queries) GetPublicV1ChannelsBySCID(ctx context.Context, arg GetPublicV1
&i.Node2Signature,
&i.Bitcoin1Signature,
&i.Bitcoin2Signature,
+ &i.Signature,
+ &i.FundingPkScript,
+ &i.MerkleRootHash,
); err != nil {
return nil, err
}
@@ -2704,7 +2751,7 @@ func (q *Queries) IsZombieChannel(ctx context.Context, arg IsZombieChannelParams
}
const listChannelsByNodeID = `-- name: ListChannelsByNodeID :many
-SELECT c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature,
+SELECT c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature, c.signature, c.funding_pk_script, c.merkle_root_hash,
n1.pub_key AS node1_pubkey,
n2.pub_key AS node2_pubkey,
@@ -2820,6 +2867,9 @@ func (q *Queries) ListChannelsByNodeID(ctx context.Context, arg ListChannelsByNo
&i.GraphChannel.Node2Signature,
&i.GraphChannel.Bitcoin1Signature,
&i.GraphChannel.Bitcoin2Signature,
+ &i.GraphChannel.Signature,
+ &i.GraphChannel.FundingPkScript,
+ &i.GraphChannel.MerkleRootHash,
&i.Node1Pubkey,
&i.Node2Pubkey,
&i.Policy1ID,
@@ -2867,7 +2917,7 @@ func (q *Queries) ListChannelsByNodeID(ctx context.Context, arg ListChannelsByNo
}
const listChannelsForNodeIDs = `-- name: ListChannelsForNodeIDs :many
-SELECT c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature,
+SELECT c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature, c.signature, c.funding_pk_script, c.merkle_root_hash,
n1.pub_key AS node1_pubkey,
n2.pub_key AS node2_pubkey,
@@ -3004,6 +3054,9 @@ func (q *Queries) ListChannelsForNodeIDs(ctx context.Context, arg ListChannelsFo
&i.GraphChannel.Node2Signature,
&i.GraphChannel.Bitcoin1Signature,
&i.GraphChannel.Bitcoin2Signature,
+ &i.GraphChannel.Signature,
+ &i.GraphChannel.FundingPkScript,
+ &i.GraphChannel.MerkleRootHash,
&i.Node1Pubkey,
&i.Node2Pubkey,
&i.Policy1ID,
@@ -3230,7 +3283,7 @@ func (q *Queries) ListChannelsWithPoliciesForCachePaginated(ctx context.Context,
const listChannelsWithPoliciesPaginated = `-- name: ListChannelsWithPoliciesPaginated :many
SELECT
- c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature,
+ c.id, c.version, c.scid, c.node_id_1, c.node_id_2, c.outpoint, c.capacity, c.bitcoin_key_1, c.bitcoin_key_2, c.node_1_signature, c.node_2_signature, c.bitcoin_1_signature, c.bitcoin_2_signature, c.signature, c.funding_pk_script, c.merkle_root_hash,
-- Join node pubkeys
n1.pub_key AS node1_pubkey,
@@ -3347,6 +3400,9 @@ func (q *Queries) ListChannelsWithPoliciesPaginated(ctx context.Context, arg Lis
&i.GraphChannel.Node2Signature,
&i.GraphChannel.Bitcoin1Signature,
&i.GraphChannel.Bitcoin2Signature,
+ &i.GraphChannel.Signature,
+ &i.GraphChannel.FundingPkScript,
+ &i.GraphChannel.MerkleRootHash,
&i.Node1Pubkey,
&i.Node2Pubkey,
&i.Policy1ID,
@@ -3436,7 +3492,7 @@ func (q *Queries) ListNodeIDsAndPubKeys(ctx context.Context, arg ListNodeIDsAndP
}
const listNodesPaginated = `-- name: ListNodesPaginated :many
-SELECT id, version, pub_key, alias, last_update, color, signature
+SELECT id, version, pub_key, alias, last_update, color, signature, block_height
FROM graph_nodes
WHERE version = $1 AND id > $2
ORDER BY id
@@ -3466,6 +3522,7 @@ func (q *Queries) ListNodesPaginated(ctx context.Context, arg ListNodesPaginated
&i.LastUpdate,
&i.Color,
&i.Signature,
+ &i.BlockHeight,
); err != nil {
return nil, err
}
diff --git a/sqldb/sqlc/migrations/000009_graph_v2.down.sql b/sqldb/sqlc/migrations/000009_graph_v2.down.sql
new file mode 100644
index 0000000..b566e40
--- /dev/null
+++ b/sqldb/sqlc/migrations/000009_graph_v2.down.sql
@@ -0,0 +1,17 @@
+-- Remove the block_height column from graph_nodes
+ALTER TABLE graph_nodes DROP COLUMN block_height;
+
+-- Remove the signature column from graph_channels
+ALTER TABLE graph_channels DROP COLUMN signature;
+
+-- Remove the funding_pk_script column from graph_channels
+ALTER TABLE graph_channels DROP COLUMN funding_pk_script;
+
+-- Remove the merkle_root_hash column from graph_channels
+ALTER TABLE graph_channels DROP COLUMN merkle_root_hash;
+
+-- Remove the block_height column from graph_channel_policies
+ALTER TABLE graph_channel_policies DROP COLUMN block_height;
+
+-- Remove the disable_flags column from graph_channel_policies
+ALTER TABLE graph_channel_policies DROP COLUMN disable_flags;
\ No newline at end of file
diff --git a/sqldb/sqlc/migrations/000009_graph_v2.up.sql b/sqldb/sqlc/migrations/000009_graph_v2.up.sql
new file mode 100644
index 0000000..19a3e99
--- /dev/null
+++ b/sqldb/sqlc/migrations/000009_graph_v2.up.sql
@@ -0,0 +1,23 @@
+-- The block height timestamp of this node's latest received node announcement.
+-- It may be zero if we have not received a node announcement yet.
+ALTER TABLE graph_nodes ADD COLUMN block_height BIGINT;
+
+-- The signature of the channel announcement. If this is null, then the channel
+-- belongs to the source node and the channel has not been announced yet.
+ALTER TABLE graph_channels ADD COLUMN signature BLOB;
+
+-- For v2 channels onwards, we cant necessarily derive the funding pk script
+-- from the other fields in the announcement, so we store it here so that
+-- we have easy access to it when we want to subscribe to channel closures.
+ALTER TABLE graph_channels ADD COLUMN funding_pk_script BLOB;
+
+-- The optional merkel root hash advertised in the V2 channel announcement.
+ALTER TABLE graph_channels ADD COLUMN merkle_root_hash BLOB;
+
+-- The block height timestamp of this channel's latest received channel-update
+-- message (for v2 channel update messages).
+ALTER TABLE graph_channel_policies ADD COLUMN block_height BIGINT;
+
+-- A bitfield describing the disabled flags for a v2 channel update.
+ALTER TABLE graph_channel_policies ADD COLUMN disable_flags SMALLINT
+ CHECK (disable_flags >= 0 AND disable_flags <= 255);
\ No newline at end of file
diff --git a/sqldb/sqlc/models.go b/sqldb/sqlc/models.go
index 24df0d6..3597200 100644
--- a/sqldb/sqlc/models.go
+++ b/sqldb/sqlc/models.go
@@ -42,6 +42,9 @@ type GraphChannel struct {
Node2Signature []byte
Bitcoin1Signature []byte
Bitcoin2Signature []byte
+ Signature []byte
+ FundingPkScript []byte
+ MerkleRootHash []byte
}
type GraphChannelExtraType struct {
@@ -72,6 +75,8 @@ type GraphChannelPolicy struct {
MessageFlags sql.NullInt16
ChannelFlags sql.NullInt16
Signature []byte
+ BlockHeight sql.NullInt64
+ DisableFlags sql.NullInt16
}
type GraphChannelPolicyExtraType struct {
@@ -85,13 +90,14 @@ type GraphClosedScid struct {
}
type GraphNode struct {
- ID int64
- Version int16
- PubKey []byte
- Alias sql.NullString
- LastUpdate sql.NullInt64
- Color sql.NullString
- Signature []byte
+ ID int64
+ Version int16
+ PubKey []byte
+ Alias sql.NullString
+ LastUpdate sql.NullInt64
+ Color sql.NullString
+ Signature []byte
+ BlockHeight sql.NullInt64
}
type GraphNodeAddress struct {
Risk score
Our methodology →Why this scored 20/100
Human-validated context
Community notes
Notes can correct, qualify, or add evidence to the AI analysis. Every note shown here has been validated by a human moderator.
No validated notes yet.
The AI analysis stands alone for now. Submit a note if you can add evidence or important context.