feat: make database migrations centric around turing complete code, rather than `up.sql`/`down.sql`

[drahnr]

Status Quo

Currently we use the vanilla diesel ORM migrations via embed_migrations!.

Limitations

We currently cannot execute rust code in lockstep in a structured way associated to sql migrations. The differentiation is also artificial. I.e. oftentimes we need to add an optional column, use rust to fill in the data, then make it mandatory which equals to SQL -> rust -> SQL sequence of execution domains.

Draft Impl

The initial impl. was a side effect of impl #1697 with the main changes in 3b1319f using a macro. However this did rely on the duality of schema and data migrations and tried to retain simplicity by continuing to use diesel_migrations::* types as much as possible.

Proposal

Directory Structure

Drop diesel_migrations and move to a fully custom approach, keeping the directory structure similar to what we have today.

I propose a letter prefix to be able to use them as rust modules which can be used without further adjustments. Note the m-prefix (yes, this is very artisan).

migrations
├── m2025062000000_setup
│   └── mod.rs
├── m2026020600000_cleanup_indices
│   └── mod.rs
├── m20260206163855_add_account_indices
│   └── mod.rs
├── m20260223160000_add_block_header_commitment
│   ├── down.sql
│   ├── mod.rs
│   └── up.sql

The next major change is relying entirely on the mod.rs rather than up.sql and down.sql - it entirely depends on the needs inside mod.rs.

This is then all picked up via a build.rs file to be included.

Inner

The expectation is, each of these contains an implementation of

trait Migration {
  fn up(&self, conn: &mut SqlConnection) -> Result<()> { .. }
  fn down(&self, conn: &mut SqlConnection) -> Result<()> { .. }
}

the build.rs the provides a list of these migrations. The ordering is derived based on lexicographical ordering of the modules.

We then can use a fixed harness to execute the sequence Vec<Box<dyn Migration>> of migrations using a fn apply.

Statefulness

We will oftentimes encounter partially updated state. To track which migrations were applied and which not, I suggest to maintain another table migrations to track which ones (by their module name) are already applied and only apply those post the last one applied.

Invariants

We cannot ever modify an existing migration post release, at least not with causing issues at the operational side.

Assumption

We care about down, if not, we might as well omit entirely which reduces trait Migration essentially to an FnOnce.