[feature]: sqldbv2: add distinct SQL database migration "tracks", decouple library from business logic

[guggero]

Today, the way SQL database migrations work with golang-migrate/migrate is that we only have a single "track" of migrations, meaning a single folder with migration files that need to be numbered in a strictly incremental way.

But because we'll have optional migrations in the future (either toggled through a CLI flag such as the --db.use-native-sql flag or through build tags to toggle not-yet-finished features in unit or integration tests), keeping things in strict order might become tricky.
Especially if there are multiple, distinct sub-projects working on migrating databases over to SQL (the existing invoice DB and the upcoming payments and graph databases), there are bound to be conflicts.
Also having those three (and in the future even more) distinct functionality sub-projects create files will lead to a hard-to-understand sequence of files.

We can start using different tracks if we change the following:

• Specify a distinct MigrationsTable for each track in pgx_migrate.Config{}. The default would be schema_migrations which we would use for existing migrations (e.g. the native invoice migrations). Any new "track" would get a different name, for example: schema_migrations_payments or schema_migrations_graph. See
• Store the files for each track in a different folder and specify that here:

  lnd/sqldb/postgres.go

  Line 182 in 7e54682

  postgresFS, driver, "sqlc/migrations", dbName, target,

As a side task it would make sense to move any of the actual migration lists or feature specific functionality out of the sqldb package so it only contains generic code that can be re-used in our other projects.

[Lymah123]

Hi @guggero and @saubyk , can I work on it?

[saubyk]

Hi @guggero and @saubyk , can I work on it?

Hi @Lymah123 since you don't have a track record of any LND contributions yet, would recommend going through the new contributor's guideline first:
https://github.com/lightningnetwork/lnd/blob/master/docs/review.md

Assuming you are familiar with how Bitcoin & Lightning Network nodes work, first focus on learning how code contributions are made to LND by reviewing existing prs. After you have a track record of a couple of pr reviews, you should think about putting up a pr of your own.

Hope this helps. thanks.

[Lymah123]

Hi @guggero and @saubyk , can I work on it?

Hi @Lymah123 since you don't have a track record of any LND contributions yet, would recommend going through the new contributor's guideline first: https://github.com/lightningnetwork/lnd/blob/master/docs/review.md

Assuming you are familiar with how Bitcoin & Lightning Network nodes work, first focus on learning how code contributions are made to LND by reviewing existing prs. After you have a track record of a couple of pr reviews, you should think about putting up a pr of your own.

Hope this helps. thanks.

Thank you @saubyk, I will start implementing your suggestions.

[Abdulkbk]

I started looking at this and will put out a PR soon.

[guggero]

Sorry, I should've mentioned this, but I've already started working on this. It's quite involved to extract the business logic out of the sqldb submodule to make the tracks useful.

[Abdulkbk]

Sorry, I should've mentioned this, but I've already started working on this. It's quite involved to extract the business logic out of the sqldb submodule to make the tracks useful.

Alright, I'll be sure to keep an eye on how you implement this in your PR then.

[guggero]

Documenting the current state of the project we now simply refer to as sqldbv2.
There has been ongoing work in multiple projects toward this end goal, which can be found here:

• lnd: https://github.com/lightningnetwork/lnd/tree/sqldb-v2-old
• tapd: https://github.com/lightninglabs/taproot-assets/tree/sqldb-v2
• litd: https://github.com/ViktorTigerstrom/lightning-terminal/tree/2025-05-sqldb-v2 and https://github.com/ViktorTigerstrom/lightning-terminal/tree/origin/2025-07-trigger-kvdb-to-sql-migrations-in-dev-builds

Main goals of sqldbv2

The main goals for refactoring the existing sqldb submodule in lnd into a version 2 are:

• Have a single library that deals with SQL related code (main focus SQLite and Postgres backends) and can be used in all our projects (to avoid things running into different directions, we already have similar code in multiple repos that should now be consolidated).
• Consolidate all the re-try, timing, persistence and other fine tuning optimizations learned from experience in the tapd and lnd repos into a single code.
• Decouple the business logic (the actual SQL queries and schemas) from the actual SQL execution logic. Today the generated code from sqlc is tightly coupled to the SQL migration and execution logic.
• Allow multiple instances of sqlc generated packages (e.g. queries and schemas for different business logic packages such as invoices, payments, graph and so on) to be used simultaneously and the code for them to be in separate places.
• Have a single mechanism for dealing with both schema migrations (e.g. SQL files) as well as data migrations (Golang code that transforms data from one version to the next or the code we run to migrate data over from bbolt or kvdb based stores to the new, native SQL stores) across all projects.

Design decisions

The above goals and some of the preliminary investigations and trial branches have lead to the following decisions:

• For data migrations (Golang-based code transformation), we're using the "post migration callback" feature added in our fork of the golang-migrate/migrate library: https://github.com/lightninglabs/migrate/?tab=readme-ov-file#forked-repository
• Because of potential references between different business logic packages (e.g. payments having foreign keys to invoice tables), it's probably not a good idea or even possible to have the SQL schemas in different packages. So we decided to have a single "prod" migration stream that contains all SQL schema migration files (and Golang data migrations) that are active by default. And then a "dev" migration stream that contains unfinished code that is still being finished and is behind a build flag for testing (allowing us to still merge code to the master branch but not activating it yet). Once a feature is ready for main release, it is "rebased" from the dev migration stream into the "prod" migration stream.
• Because the queries and data structs generated by sqlc are dependent on the schema version they are generated at, we need to have a way that allows us to execute Golang data migration code for a certain database version. The plan is to freeze the sqlc generated query and data struct code at a certain time into their own packages, similar to how we did it with serialization/deserialization code in the channeldb/migrationXX packages. So when we need to create a new data migration, we take a snapshot of the sqlc generated code at that point in time, copy it into a new migrationXX package, potentially manually prune it to only contain what we actually need for the data migration, then use and commit that in the PR.

Dependencies for shipping native SQL in lnd

Being able to create future data migrations, we need the sqlc query snapshot mechanism which requires the sqlc decoupling which requires the whole base refactor from sqldb to sqldbv2.

Things to figure out

Using the lnd sqldbv2 library in tapd is pretty straightforward, as there is no big change needed. But upgrading lnd's code to use the new v2 library itself has a couple of challenges:

• The code in the V1 sqldb package uses its own migration_tracker SQL table to track what migration versions have already been applied.
• The V2 code will use the default schema_migrations table used by our fork of the golang-migrate/migrate library.
• We need to implement a way that transforms the content of the old migration_tracker table into the schema_migrations table without relying on the migrate library itself (so before the new migrate library runs for the first time).
• If we have a prod and a dev stream, what happens if you have ALTER ... statements in the dev stream schema migrations? Because currently the sqlc generated query code will generate stuff from the latest version of the dev stream.
• If a SQL migration succeeds but its corresponding code migration in the "post migration callback" errors, the code migration will not be re-run on the next startup. This issue needs to be addressed in the forked migration repository: https://github.com/lightninglabs/migrate

Release plan

The release of sqldb/v2 will be carried out in the following phases:

1. Merge a working version of sqldb/v2, rebased on lnd master, into a separate sqldb/v2 package on a dedicated lnd branch: https://github.com/lightningnetwork/lnd/tree/sqldb-v2
2. Add the separate sqldbv2 package into lnd master.
3. Ensure that all repositories currently using sqldb "v1" migrate to sqldb/v2.
4. Phase out and replace the original sqldb "v1" package in lnd with sqldb/v2, making v2 the default sqldb version.