How to Perform Database Schema Migrations in Fincept's SQLite Storage Layer

FinceptTerminal automatically applies database schema migrations at startup using a versioned registry system that executes SQL transactions through the MigrationRunner class.

FinceptTerminal stores all persistent data in a single SQLite file managed by the fincept::Database singleton. When the database initializes, the built-in migration framework ensures the schema matches the current codebase version. This article explains how the migration system works in the Fincept-Corporation/FinceptTerminal repository and how to add new schema changes safely.

How the Migration System Works

The migration architecture consists of four coordinated components: a static registry, a runner that executes transactions, versioned migration files, and explicit registration functions.

Migration Registry

The system maintains a static QVector<Migration> that holds every migration defined in the application. This registry is declared in [fincept-qt/src/storage/sqlite/migrations/MigrationRunner.h](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/fincept-qt/src/storage/sqlite/migrations/MigrationRunner.h) and populated via MigrationRunner::register_migration(). Each Migration struct contains a version number, descriptive name, and an apply function pointer.

Migration Runner Execution

The MigrationRunner class, implemented in [MigrationRunner.cpp](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/fincept-qt/src/storage/sqlite/migrations/MigrationRunner.cpp), orchestrates the actual schema changes. When runner.run() executes, it performs the following steps:

1. Creates or verifies the schema_version table to track applied migrations.
2. Reads the current schema version using read_current_version().
3. Iterates through the sorted registry and executes every migration with a version greater than the current one.
4. Wraps each migration in an immediate SQLite transaction, committing only after the migration's apply lambda succeeds and the version is recorded.

If any migration fails, the transaction rolls back, leaving the database in its previous consistent state.

Versioned Migration Files

Individual migrations reside in fincept-qt/src/storage/sqlite/migrations/vNNN_*.cpp files. Each file implements apply_vNNN(QSqlDatabase&) which contains the SQL statements for that schema version. The file also defines register_migration_vNNN(), which calls MigrationRunner::register_migration() with the version number, name, and apply function.

Explicit Registration Pattern

To prevent MSVC linker stripping of static initializers, FinceptTerminal uses explicit registration. The main entry point in [fincept-qt/src/app/main.cpp](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/fincept-qt/src/app/main.cpp) calls every register_migration_vXXX() function before opening the database. This ensures all migrations are present in the registry regardless of optimization settings.

Running Migrations Automatically

Migrations execute automatically when the Database singleton opens the SQLite file. The sequence occurs in Database::open() as implemented in Database.cpp:

fincept::Database& db = fincept::Database::instance();
if (auto rc = db.open("/path/to/fincept.db"); rc.is_err()) {
    qFatal("Failed to open DB: %s", rc.error().c_str());
}

The internal flow inside Database::open() follows this pattern:

// 1. Add database connection
db_ = QSqlDatabase::addDatabase("QSQLITE", "fincept_main");
db_.setDatabaseName(path);
db_.open();

// 2. Apply pragmas (WAL mode, foreign keys, etc.)
apply_pragmas();

// 3. Run migrations
MigrationRunner runner(db_);
return runner.run();  // Applies pending schema changes

This guarantees that every application start operates on the correct schema version without manual intervention.

Adding a New Database Schema Migration

To modify the FinceptTerminal database schema, create a versioned migration file and register it in the application entry point.

1. Create the migration source file under fincept-qt/src/storage/sqlite/migrations/ with the next sequential version number (e.g., v019_new_feature.cpp).

2. Implement the apply function that executes your schema changes:

#include "storage/sqlite/migrations/MigrationRunner.h"
#include <QSqlError>
#include <QSqlQuery>

namespace fincept {
namespace {

static Result<void> sql(QSqlDatabase& db, const char* stmt) {
    QSqlQuery q(db);
    if (!q.exec(stmt))
        return Result<void>::err(q.lastError().text().toStdString());
    return Result<void>::ok();
}

Result<void> apply_v019(QSqlDatabase& db) {
    // Defensive check: skip if column already exists
    QSqlQuery check(db);
    if (check.exec("PRAGMA table_info(settings)") && check.next()) {
        return Result<void>::ok();
    }
    
    return sql(db, "ALTER TABLE settings ADD COLUMN description TEXT DEFAULT ''");
}

} // anonymous namespace

void register_migration_v019() {
    static bool done = false;
    if (done) return;
    done = true;
    MigrationRunner::register_migration({19, "add_settings_description", apply_v019});
}

} // namespace fincept

3. Add the registration call in fincept-qt/src/app/main.cpp after existing registration calls:

// Existing registrations
fincept::register_migration_v001();
// ... other versions ...
fincept::register_migration_v019();  // Add this line

4. Rebuild and run FinceptTerminal. The MigrationRunner will automatically detect version 19 as pending and apply the schema change within a transaction.

Key Files in the Migration System

File	Purpose
fincept-qt/src/storage/sqlite/Database.h	Singleton wrapper exposing open(), execute(), and transaction helpers
fincept-qt/src/storage/sqlite/Database.cpp	Implements database opening, pragma configuration, and triggers MigrationRunner
fincept-qt/src/storage/sqlite/migrations/MigrationRunner.h	Declares Migration struct and MigrationRunner class with registration API
fincept-qt/src/storage/sqlite/migrations/MigrationRunner.cpp	Executes pending migrations, manages schema_version table, handles transactions
fincept-qt/src/storage/sqlite/migrations/v001_initial.cpp	Example migration creating the initial schema
fincept-qt/src/app/main.cpp	Application entry point that explicitly registers all migrations

Summary

• Automatic execution: FinceptTerminal runs all pending database schema migrations automatically when Database::open() initializes the SQLite connection.
• Transactional safety: Each migration executes within an immediate SQLite transaction; failures roll back changes and halt the process.
• Versioned registry: Migrations register via MigrationRunner::register_migration() with sequential version numbers, stored in schema_version table.
• Explicit registration: Developers must add register_migration_vXXX() calls in main.cpp to prevent linker optimization from stripping static initializers.
• Idempotent patterns: Migration functions should defensive-check schema state (e.g., PRAGMA table_info) to handle partial failures gracefully.

Frequently Asked Questions

How does FinceptTerminal handle database schema migrations on startup?

When the application launches, Database::open() creates a MigrationRunner instance and calls runner.run(). This checks the schema_version table, identifies any registered migrations with higher version numbers, and executes them sequentially inside transactions. The process is fully automatic and requires no manual intervention.

What happens if a database schema migration fails?

If a migration's apply function returns an error or throws an exception, MigrationRunner rolls back the active transaction. This leaves the database at the last successfully applied version and prevents partial schema changes. The application will typically fail to start, logging the specific SQL error that caused the rollback.

Why do I need to manually register migrations in main.cpp?

FinceptTerminal uses explicit registration functions like register_migration_v019() in fincept-qt/src/app/main.cpp because static initialization blocks in individual translation units can be stripped by the MSVC linker during optimized builds. Explicit calls ensure every migration object is linked into the final binary and added to the MigrationRunner registry.

Can multiple developers add migrations simultaneously?

Yes, but coordination is required for version numbers. Each migration must have a unique sequential integer version. If two developers both create "v019", collisions will occur. The recommended workflow is to claim the next available version number in the project's issue tracker or pull request description before implementation, ensuring sequential integrity in the schema_version table.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}