db: use native SQL

[ellemouton]

Overview:

We should follow the example set by LND and migrate our kvdb stores to SQL. Luckily, LiTs set of tables is quite small and so this is quite a contained task. We also have never supported the kv schema in other backend options like sql or postgres and don't currently have any tables in any sql backend. This is nice because it keeps set of possible starting points to a size of 1: Migrate bbolt kvstore data to native sql stores (either postgres or sqlite). I think that since currently we only store LNC data in LiT's DB, nothing is critical and so I think we should force the migration at the end of the day and not necessarily let uses opt to not run the migration.

Getting started:

We can get started by:

1. defining the new SQL tables (dont actually trigger them in anything other than tests)
2. defining the SQL queries & putting them behind an interface such that all current unit tests can be run against both the kvdb backends and new sql backends.

The above can be done for the accounts, actions, session and firewalldb stores.

We can get started on the work for the above and merge things well before we need to worry about the final switch to sql.

End goal:

By the end of the project, we want a single SQL backend with the following relations.

Action Plan/Progress tracking

Phase 1: Defining Schemas

By the end of phase 1, we want to be able to spin up LiT in dev mode with a full SQL backend.
To do this, we will follow the following conversion strategy for each DB store:

1. massage the given Store interface into one that is more SQL compatible
2. Make any changes to the unit tests and KVDB impl that will make that unit tests runnable against all DB backends.
3. Define the SQL schemas & queries
4. Implement the SQL version of the Store interface (CRUD layer)
5. Get the CI to run all that DB's unit tests against all possible backends.
6. get the itests running against the new backends

We will do the above steps for each of the following and in the following order:

A) The Accounts store
B) Sessions store
C) rules kvstore
D) rules privacy mapper store
E) actions store.

Phase 2: Migration code

Here we will write the migration functions for each of the tables plus thorough tests for them. At the end of this phase, we are still not exposing any new functionality to a prod release.

For each database store, we will:

1. Implement the migration function.
   When invoked with a specific KVDB store, this function will copy all data from that KVDB store into the corresponding SQLB store.

2. Create unit tests.

   • Populate a KVDB instance with every possible combination of data relevant to that store, as well as randomised data.
   • Run the migration function against that KVDB instance and verify that the data is successfully migrated.

3. Ensure that the unit tests is executed successfully against postgres & sqllite backends.

4. Manually verify the migration locally.

   • Use a modified litd instance with the dev build flag enabled to expose the migration.
   • Confirm that the migration runs without errors.

We will repeat these four steps for each of the following stores, in the order listed:

A) The Accounts store
B) Sessions store
C) rules kvstore
D) rules privacy mapper store
E) actions store.

Phase 3: Calling the migration code & handling user config

Finally, we will invoke the migration logic and add configuration options to switch to a new backend. This process will be carried out in the following steps:

1. Update litd to use sqldb-v2.

2. Use sqldb-v2 to run the standard SQL migrations in litd, and add support that ensures that the exact set of SQLC models and queries that existed when each migration was introduced can be used.

3. Add a migration step to the SQL migration list that transfers data from KVDB to SQL. Initially, this step will only run if the dev migrate-to-sql flag is set.

4. Add an integration test that:

   • Starts a litd instance using a bbolt databasebackend.
   • Populates all relevant stores.
   • Restarts litd with an SQL backend and the migrate-to-sql flag enabled.
   • Verifies that the migration completes successfully.

5. Remove the migrate-to-sql flag and make KVDB-to-SQL migrations run automatically whenever an SQL backend is selected. Note: at this stage, the sqlite & postgres backends will only be supported in development builds.

6. Introduce a disable-kvdb-to-sql-migration configuration flag. When set, this flag will prevent the KVDB→SQL migration from running.

7. Perform extensive manual testing of KVDB→SQL migrations, especially on large litd nodes with substantial data volumes.

8. Enable the sqlite and postgres databasebackend options in the production configuration, and designate one of them as the default.

9. Once a node has successfully migrated from KVDB to SQL:

   • Delete the data stored in the Bbolt DB.
   • Prevent litd from starting if bbolt is still configured as the databasebackend.

Phase 4: Removing support for bbolt database backends

After sufficient time has passed and we are confident that KVDB→SQL migrations will succeed for all users, we will remove BoltDB support by:

1. Removing support for the the disable-kvdb-to-sql-migration configuration flag.
2. Removing bbolt from the list of supported databasebackend options.

Progress Checklists

Phase 1 (Schemas/Queries/CRUD)

### Accounts: - [x] Prep/Cleanup - [x] Prepare the accounts `Store` interface to be ready for a SQL impl - [x] Prep unit tests to be ready to pass against a different backend - [x] #956 - [x] implement the SQL CRUD (ie, the SQL version of the Store interface). - [x] Run the above impl against the existing unit tests (part of CI) - [x] CI step for getting itests to run against LiT with SQL store for accounts

Sessions:

• Prep/Cleanup
• Prepare the sessions Store interface to be ready for a SQL impl #966
• Prep unit tests to be ready to pass against a different backend
• Define the SQL schemas and queries
• implement the SQL CRUD (ie, the SQL version of the Store interface).
• Run the above impl against the existing unit tests (part of CI)
• CI step for getting itests to run against LiT with SQL store for sessions

KV stores

• Prep/Cleanup
• Prepare the kvstores interface to be ready for a SQL impl
• Prep unit tests to be ready to pass against a different backend
• Define the SQL schemas and queries
• implement the SQL CRUD (ie, the SQL version of the Store interface).
• Run the above impl against the existing unit tests (part of CI)
• CI step for getting itests to run against LiT with SQL store for kvstores

Privacy Pairs

• Prep/Cleanup
• Prepare the privacy map store interface to be ready for a SQL impl
• Prep unit tests to be ready to pass against a different backend
• Define the SQL schemas and queries
• implement the SQL CRUD (ie, the SQL version of the Store interface).
• Run the above impl against the existing unit tests (part of CI)
• CI step for getting itests to run against LiT with SQL store for privacy pairs

Actions

• Prep/Cleanup
• Prepare the actions store interface to be ready for a SQL impl
• Prep unit tests to be ready to pass against a different backend
• Define the SQL schemas and queries
• implement the SQL CRUD (ie, the SQL version of the Store interface).
• Run the above impl against the existing unit tests (part of CI)
• CI step for getting itests to run against LiT with SQL store for actions

Phase 2 (Migration)

• Accounts store migration implementation
• Sessions store migration implementation
• rules kvstore migration implementation
• rules privacy migration implementation
• actions store migration implementation

Phase 3 (Integration)

• Use sqldb-v2 in litd.
• Use sqldb-v2 to run sql migrations with the correct models & queries
• Add the kvdb to sql migration step
• Add itest coverage
• Automatically trigger the kvdb to sql migration from SQL backends
• Introduce a disable-kvdb-to-sql-migration configuration flag
• Extensive manual testing of KVDB→SQL migrations
• Enable the sqlite and postgres databasebackend for the prod conf
• Ensure successfully migrated nodes can't use bbolt backends

[ellemouton]

cc @levmi for input re the two approach options

EDIT: realised we do actually need to migrate. can discuss offline

[ellemouton]

TODO(elle): possibly use uints for timestamps instead of TIMESTAMP

[Roasbeef]

possibly use uints for timestamps instead of TIMESTAMP

With the way it works to sqlc, if you use TIMESTAMP, then it gets stored as a string in Go formatting (time.Time). You'd need to take care to ensure times are normalized to UTC timezone before inserting. Going with this approach over just an integer has a nice effect as you can do a bit more expressive queries as you can use the like operator.