# How AppFlowy Handles Database Migrations for User Data: A Technical Deep Dive

> Learn how AppFlowy manages database migrations for user data. Explore its version-aware pipeline, automatic startup execution, and SQLite history tracking for data integrity.

- Repository: [AppFlowy-IO/AppFlowy](https://github.com/AppFlowy-IO/AppFlowy)
- Tags: deep-dive
- Published: 2026-03-03

---

**AppFlowy handles database migrations for user data through a version-aware pipeline that runs automatically on application startup, using a SQLite-backed history tracking system to ensure each transformation executes exactly once while maintaining data integrity across application updates.**

AppFlowy stores all user-generated content locally in an **SQLite** database, requiring a robust migration system to evolve the schema and data formats without losing user work. According to the AppFlowy-IO/AppFlowy source code, the migration framework is implemented in the `flowy-user` Rust crate and executes immediately when a user session initializes.

## The Migration Entry Point

When a user launches the application, the migration process begins inside **`UserManager::init_with_callback`** located in [`frontend/rust-lib/flowy-user/src/user_manager/manager.rs`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/frontend/rust-lib/flowy-user/src/user_manager/manager.rs) (lines 29-34). This routine performs four critical steps:

1. **Retrieve the current session** – loads the user's `user_id` and `workspace_id`.
2. **Open the SQLite connection** for the specific user.
3. **Determine the authentication type** (local, AppFlowy Cloud, etc.).
4. **Execute the data-migration pipeline** via the `run_data_migration` function (lines 75-85).

The `run_data_migration` function constructs a vector of migration objects and delegates execution to the migration engine:

```rust
let migrations = collab_migration_list();
UserLocalDataMigration::new(...).run(migrations, user_auth_type, app_version);

```

## The Migration Pipeline Architecture

The core migration engine resides in **`UserLocalDataMigration::run`** at [`frontend/rust-lib/flowy-user/src/migrations/migration.rs`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/frontend/rust-lib/flowy-user/src/migrations/migration.rs) (lines 41-56). This implementation follows an idempotent, history-tracking pattern that prevents duplicate execution.

### Migration History Tracking

Before running any transformations, the engine loads the **migration history** from the `user_data_migration_records` table (lines 62-65). This SQLite table stores records of previously completed migrations to ensure idempotency.

For each migration in the execution list, the engine performs two validation checks:

1. **Duplicate detection** – searches the history records to verify the migration has not already run.
2. **Version applicability** – invokes the migration's `run_when` method, passing the *first installed version* (stored under the key `first_install_version`) and the current application version.

If both checks pass, the engine executes the migration's `run` method and persists a new record to the history table via `save_migration_record` (lines 86-90).

### Version-Aware Execution Logic

Each migration implements conditional logic to determine whether it should execute based on the user's upgrade path. For example, `FavoriteV1AndWorkspaceArrayMigration` in [`workspace_and_favorite_v1.rs`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/workspace_and_favorite_v1.rs) (lines 28-37) only runs for installations older than version 0.4.0, ensuring that users who started on newer versions skip legacy transformation logic.

## Migration Definition and Traits

Every database migration in AppFlowy implements the **`UserDataMigration`** trait, which defines three required methods:

```rust
pub trait UserDataMigration {
    fn name(&self) -> &str;
    fn run_when(&self, first_installed_version: &Option<Version>, current_version: &Version) -> bool;
    fn run(&self, user: &Session, collab_db: &Weak<CollabKVDB>,
            user_auth_type: &AuthType, db: &mut SqliteConnection,
            store_preferences: &Arc<KVStorePreferences>) -> FlowyResult<()>;
}

```

- **`name`** – returns a unique string identifier used for the history table lookup.
- **`run_when`** – contains version comparison logic to determine applicability.
- **`run`** – executes the actual data transformation, often utilizing the **Collab** library to manipulate document structures and folder hierarchies.

## Migration Ordering and Registration

The execution order is strictly defined in the **`collab_migration_list`** function within [`manager.rs`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/manager.rs) (lines 54-63). This function returns a vector of boxed trait objects:

```rust
fn collab_migration_list() -> Vec<Box<dyn UserDataMigration>> {
    vec![
        Box::new(HistoricalEmptyDocumentMigration),
        Box::new(FavoriteV1AndWorkspaceArrayMigration),
        Box::new(WorkspaceTrashMapToSectionMigration),
        Box::new(CollabDocKeyWithWorkspaceIdMigration),
        Box::new(AnonUserWorkspaceTableMigration),
    ]
}

```

**Critical rule:** New migrations must be appended to the end of this vector. This preserves the deterministic order required for incremental schema changes, ensuring that later migrations can depend on transformations applied by earlier ones.

## First-Time Installation Handling

For brand-new users, AppFlowy optimizes the startup path by avoiding unnecessary work. When a fresh database is detected, `set_first_time_installed_version` stores the current application version under the `first_install_version` key. The system then invokes `mark_all_migrations_as_applied` (lines 66-73), which records all existing migrations as completed in the history table without executing their transformation logic.

This approach ensures that new users start with the modern schema while maintaining compatibility with the migration tracking system.

## Implementing a Custom Migration

To add a new database migration for user data in AppFlowy, create a struct implementing the `UserDataMigration` trait and register it in the migration list:

```rust
// src/migrations/add_new_field.rs
pub struct AddNewFieldMigration;

impl UserDataMigration for AddNewFieldMigration {
    fn name(&self) -> &str {
        "add_new_field_migration"
    }

    fn run_when(
        &self,
        first_installed_version: &Option<Version>,
        _current_version: &Version,
    ) -> bool {
        // Only run for users who installed before version 0.9.0
        match first_installed_version {
            None => true,
            Some(v) => v < &Version::new(0, 9, 0),
        }
    }

    fn run(
        &self,
        _user: &Session,
        _collab_db: &Weak<CollabKVDB>,
        _auth: &AuthType,
        db: &mut SqliteConnection,
        _store: &Arc<KVStorePreferences>,
    ) -> FlowyResult<()> {
        diesel::sql_query("ALTER TABLE folder ADD COLUMN new_field TEXT")
            .execute(db)?;
        Ok(())
    }
}

```

Register the migration in [`manager.rs`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/manager.rs):

```rust
fn collab_migration_list() -> Vec<Box<dyn UserDataMigration>> {
    vec![
        // ... existing migrations
        Box::new(AddNewFieldMigration), // Append at the end
    ]
}

```

## Summary

- **AppFlowy stores user data in SQLite** and runs migrations automatically during session initialization via `UserManager::init_with_callback`.
- **The `UserLocalDataMigration` engine** tracks executed migrations in the `user_data_migration_records` table to ensure idempotency.
- **Version-aware logic** via the `run_when` method allows migrations to target specific upgrade paths without affecting new installations.
- **Strict ordering** is maintained through the `collab_migration_list` vector, requiring new migrations to be appended at the end.
- **Fresh installations bypass execution** through `mark_all_migrations_as_applied`, recording all current migrations as already applied.

## Frequently Asked Questions

### How does AppFlowy prevent the same migration from running twice?

The migration engine queries the `user_data_migration_records` SQLite table before executing any migration. Each migration must implement a unique `name()` method, and the engine compares this identifier against the history records. If a match exists, the migration is skipped entirely, ensuring idempotent execution across app restarts.

### What happens to database migrations when a new user installs AppFlowy for the first time?

During first-time initialization, the system stores the current app version under the `first_install_version` key and immediately calls `mark_all_migrations_as_applied`. This marks every existing migration as completed in the history table without executing their transformation code, allowing new users to start with the current schema while maintaining compatibility with the tracking system.

### Can migrations be targeted to specific AppFlowy versions?

Yes, each migration implements the `run_when` method, which receives both the `first_installed_version` and `current_version` as `semver::Version` objects. Developers can implement conditional logic to run migrations only for users upgrading from specific legacy versions, such as checking if `first_installed_version < Version::new(0, 4, 0)` before executing schema transformations.

### Where are the migration definitions stored in the AppFlowy codebase?

Migration implementations are located in `frontend/rust-lib/flowy-user/src/migrations/`, with the orchestration logic in [`manager.rs`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/manager.rs). Specific migration examples include [`workspace_and_favorite_v1.rs`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/workspace_and_favorite_v1.rs) for workspace structure updates and [`anon_user_workspace.rs`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/anon_user_workspace.rs) for anonymous user table creation. The trait definition and execution engine reside in [`migration.rs`](https://github.com/AppFlowy-IO/AppFlowy/blob/main/migration.rs).