Invercargill-Sql/plans/phase-8-namespaces.md

Phase 8: Migration Namespaces

Overview

This document describes the design for adding namespace support to the migration system. Namespaces allow libraries to share the same database while maintaining their own migration histories without interfering with each other's migration order.

Problem Statement

Without namespaces, all migrations share a single version sequence. If a library provides migrations with versions 1, 2, 3 and the application also uses 1, 2, 3, they would conflict. Libraries cannot safely ship migrations that integrate with an application's database.

Design Goals

• Namespace isolation: Each namespace has its own migration sequence
• Cross-namespace dependencies: Migrations can declare dependencies on other namespaces
• Safe foreign keys: Libraries can create tables with foreign keys to other libraries' tables
• Explicit ordering: Dependencies determine cross-namespace execution order
• Time-based rollback: Rolling back to a point in time affects all namespaces consistently

Architecture

Migration Class Changes

The Migration abstract class is extended with namespace support:

public abstract class Migration : Object {
    /**
     * The serial number of this migration within its namespace.
     * 
     * Serials must be unique within a namespace and are used to determine
     * the order of migrations within that namespace. They do not need to
     * be sequential (e.g., 20260112, 20260315 are valid).
     * 
     * Migrations within a namespace are executed in ascending serial order.
     */
    public abstract uint64 serial { get; }
    
    /**
     * The namespace this migration belongs to.
     *
     * Namespaces isolate migration histories. Each namespace maintains
     * its own sequence of applied migrations. Libraries should use a
     * unique namespace (e.g., "auth", "logging", "my_library").
     *
     * This property is abstract and must be implemented by all migrations.
     * Named `migration_namespace` to avoid conflict with Vala's `namespace` keyword.
     */
    public abstract string migration_namespace { get; }
    
    /**
     * A human-readable name for this migration.
     */
    public abstract string name { get; }
    
    /**
     * Dependencies on other namespaces or specific migrations.
     * 
     * Dependencies are declared as strings in one of two formats:
     * - "namespace" - Any migration in that namespace must be applied
     * - "namespace:serial" - The specific migration must be applied
     * 
     * Examples:
     * - {"auth"} - Auth namespace must have at least one migration applied
     * - {"auth:2"} - Auth namespace must have migration serial 2 applied
     * - {"auth:2", "logging"} - Both conditions must be met
     * 
     * Default implementation returns an empty array (no dependencies).
     */
    public virtual string[] dependencies { get { return {}; } }
    
    /**
     * Apply the migration forward.
     */
    public abstract void up(MigrationBuilder b) throws SqlError;
    
    /**
     * Roll back the migration.
     */
    public abstract void down(MigrationBuilder b) throws SqlError;
}

Migration Tracking Table

The __migrations table is updated to support namespaces:

CREATE TABLE __migrations (
    application_order INTEGER PRIMARY KEY AUTOINCREMENT,
    namespace TEXT NOT NULL,
    serial INTEGER NOT NULL,
    name TEXT NOT NULL,
    applied_at INTEGER NOT NULL,
    UNIQUE (namespace, serial)
);

Key changes from previous design:

• application_order: Tracks the exact order migrations were applied across all namespaces
• namespace: The migration's namespace
• serial: The migration's serial number (was version)
• Composite unique constraint on (namespace, serial)

Dependency Syntax

Dependencies are declared as strings with the following semantics:

Dependency String	Meaning
"auth"	At least one migration in namespace "auth" must be applied
"auth:2"	Migration with serial 2 in namespace "auth" must be applied (exact match)
"auth:0"	Migration with serial 0 in namespace "auth" must be applied
":1"	Invalid - parse error
"auth:"	Invalid - parse error
"auth:1:extra"	Invalid - parse error

Migration Ordering

Migrations are ordered using two mechanisms:

1. Within namespace: Sorted by ascending serial number

   • Migrations with serials 6, 2, 9 run in order: 2 → 6 → 9

2. Across namespaces: Determined by dependency graph (topological sort)

   • If app:1 depends on auth:2, then auth:2 must run before app:1

   • Registration order does not affect execution order

     flowchart TB
     subgraph auth_namespace [auth namespace]
     auth_v1[auth:1 - CreateUsers]
     auth_v2[auth:2 - AddRoles]
     end
         
     subgraph app_namespace [app namespace]
     app_v1[app:1 - CreateOrders<br/>depends on auth:1]
     app_v2[app:2 - AddUserOrders<br/>depends on auth:2]
     end
         
     auth_v1 --> auth_v2
     auth_v1 --> app_v1
     auth_v2 --> app_v2
         
     style app_v1 fill:#f9f,stroke:#333
     style app_v2 fill:#f9f,stroke:#333
     

Rollback Behavior

Rollbacks are time-based, not dependency-based. When rolling back to a specific migration, all migrations applied after that point are rolled back in reverse application order.

Example:

Applied order:

1. auth:1
2. auth:2
3. app:5 (depends on auth:2)
4. logging:1 (no dependencies)

Rollback to auth:1:

1. Roll back logging:1 (applied 4th)
2. Roll back app:5 (applied 3rd)
3. Roll back auth:2 (applied 2nd)
4. Stop - auth:1 remains

This ensures the database is always in a historically consistent state.

flowchart LR
    subgraph Applied [Applied Order]
        A1[1. auth:1]
        A2[2. auth:2]
        A3[3. app:5]
        A4[4. logging:1]
    end
    
    subgraph Rollback [Rollback to auth:1]
        R1[1. Roll back logging:1]
        R2[2. Roll back app:5]
        R3[3. Roll back auth:2]
        R4[4. Done - auth:1 remains]
    end
    
    A1 --> A2 --> A3 --> A4
    R1 --> R2 --> R3 --> R4

Circular Dependency Detection

The migration runner must detect circular dependencies at registration time or when migrate_to_latest() is called. If a cycle is detected, an error is thrown with a clear message:

Circular dependency detected: auth:2 → app:1 → logging:1 → auth:2

MigrationRunner API Changes

The MigrationRunner API is extended with optional namespace parameters:

public class MigrationRunner : Object {
    
    /**
     * Creates a new migration runner.
     */
    public MigrationRunner(Connection connection, SqlDialect dialect);
    
    /**
     * Registers a migration instance.
     */
    public void register_migration(Migration migration);
    
    /**
     * Gets all applied migrations, optionally filtered by namespace.
     */
    public Vector<MigrationRecord> get_applied_migrations(string? namespace = null);
    
    /**
     * Gets all pending migrations, optionally filtered by namespace.
     */
    public Vector<Migration> get_pending_migrations(string? namespace = null);
    
    /**
     * Applies all pending migrations across all namespaces.
     * Dependencies are resolved to determine execution order.
     */
    public void migrate_to_latest() throws SqlError;
    
    /**
     * Migrates a specific namespace to its latest version.
     * Dependencies from other namespaces are applied first if needed.
     */
    public void migrate_to_latest(string namespace) throws SqlError;
    
    /**
     * Migrates to a specific migration.
     * All dependencies are applied first if needed.
     * If rolling back, uses time-based rollback.
     */
    public void migrate_to(string namespace, uint64 serial) throws SqlError;
    
    /**
     * Rolls back the last N migrations across all namespaces.
     */
    public void rollback(int steps = 1) throws SqlError;
    
    /**
     * Rolls back to a specific migration using time-based rollback.
     * All migrations applied after the target are rolled back.
     */
    public void rollback_to(string namespace, uint64 serial) throws SqlError;
    
    /**
     * Rolls back all migrations across all namespaces.
     */
    public void rollback_all() throws SqlError;
    
    /**
     * Gets the current highest serial for a namespace.
     * Returns 0 if no migrations have been applied.
     */
    public uint64 get_current_serial(string namespace) throws SqlError;
    
    /**
     * Validates the dependency graph for cycles.
     * Throws SqlError if a cycle is detected.
     */
    public void validate_dependencies() throws SqlError;
}

Example Usage

Library Migrations

Auth library (auth namespace):

public class Auth_V001_CreateUsers : Migration {
    public override string migration_namespace { get { return "auth"; } }
    public override uint64 serial { get { return 1; } }
    public override string name { get { return "CreateUsers"; } }
    // No dependencies - this is a base migration
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("users", t => {
            t.column<int64?>("id").primary_key().auto_increment();
            t.column<string>("email").not_null().unique();
            t.column<string>("password_hash").not_null();
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("users");
    }
}

public class Auth_V002_AddRoles : Migration {
    public override string migration_namespace { get { return "auth"; } }
    public override uint64 serial { get { return 2; } }
    public override string name { get { return "AddRoles"; } }
    // No external dependencies
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("roles", t => {
            t.column<int64?>("id").primary_key().auto_increment();
            t.column<string>("name").not_null().unique();
        });
        
        b.alter_table("users", t => {
            t.add_column<int64?>("role_id")
                .references("roles", "id");
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.alter_table("users", t => {
            t.drop_column("role_id");
        });
        b.drop_table("roles");
    }
}

Application (app namespace) with dependencies on auth:

public class App_V001_CreateOrders : Migration {
    public override string migration_namespace { get { return "app"; } }
    public override uint64 serial { get { return 1; } }
    public override string name { get { return "CreateOrders"; } }
    
    // Requires auth namespace to have users table
    public override string[] dependencies { get { return {"auth"}; } }
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("orders", t => {
            t.column<int64?>("id").primary_key().auto_increment();
            t.column<int64?>("user_id").not_null()
                .references("users", "id");  // FK to auth.users
            t.column<double>("total").not_null();
            t.column<string>("status").not_null();
        });
        
        b.create_index("idx_orders_user_id")
            .on_table("orders")
            .columns("user_id");
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_index("idx_orders_user_id");
        b.drop_table("orders");
    }
}

public class App_V002_CreateOrderItems : Migration {
    public override string migration_namespace { get { return "app"; } }
    public override uint64 serial { get { return 20260320 }; }  // Date-based serial
    public override string name { get { return "CreateOrderItems"; } }
    
    // Requires auth:1 specifically (users table) and app:1 (orders table)
    public override string[] dependencies { get { return {"auth:1", "app:1"}; } }
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("order_items", t => {
            t.column<int64?>("id").primary_key().auto_increment();
            t.column<int64?>("order_id").not_null()
                .references("orders", "id");
            t.column<string>("product_name").not_null();
            t.column<int>("quantity").not_null();
            t.column<double>("price").not_null();
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("order_items");
    }
}

Running Migrations

void main() {
    try {
        var conn = ConnectionFactory.create_and_open("sqlite:///myapp.db");
        var dialect = new SqliteDialect();
        var runner = new MigrationRunner(conn, dialect);
        
        // Register migrations from all libraries
        // Auth library
        runner.register_migration(new Auth_V001_CreateUsers());
        runner.register_migration(new Auth_V002_AddRoles());
        
        // Application
        runner.register_migration(new App_V001_CreateOrders());
        runner.register_migration(new App_V002_CreateOrderItems());
        
        // Validate dependencies (detects cycles)
        runner.validate_dependencies();
        
        // Apply all pending migrations in dependency order
        runner.migrate_to_latest();
        
        // Or migrate a specific namespace
        // runner.migrate_to_latest("app");
        
        // Or migrate to a specific point
        // runner.migrate_to("auth", 2);
        
        // Rollback examples
        // runner.rollback(1);  // Roll back last migration
        // runner.rollback_to("auth", 1);  // Roll back to auth:1
        
        conn.close();
    } catch (SqlError e) {
        stderr.printf("Migration error: %s\n", e.message);
    }
}

Error Handling

Missing Dependency

When a dependency cannot be satisfied:

SqlError: Unsatisfied dependency: app:1 requires auth:2 but no migration 
with serial 2 is registered in namespace 'auth'

Circular Dependency

When a cycle is detected:

SqlError: Circular dependency detected: auth:2 → app:1 → logging:1 → auth:2

Missing Namespace

When a dependency references a namespace with no registered migrations:

SqlError: Unsatisfied dependency: app:1 requires namespace 'auth' but no 
migrations are registered in that namespace

Invalid Dependency Syntax

When a dependency string is malformed:

SqlError: Invalid dependency syntax: 'auth:' - expected 'namespace' or 
'namespace:serial'

Implementation Checklist

• Update Migration class
  • Rename version to serial
  • Change serial type from int to uint64
  • Add abstract migration_namespace property
  • Add virtual dependencies property with empty default
• Update __migrations table schema
  • Add application_order column (auto-increment)
  • Add namespace column
  • Rename version to serial
  • Add composite unique constraint on (namespace, serial)
• Create Dependency parser class
  • Parse "namespace" format
  • Parse "namespace:serial" format
  • Validate syntax and throw on invalid format
• Update MigrationRunner
  • Track application order in migrations table
  • Implement dependency graph building
  • Implement topological sort for execution order
  • Implement circular dependency detection
  • Implement time-based rollback using application_order
  • Add namespace parameter to existing methods
  • Add validate_dependencies() method
  • Add get_current_serial(namespace) method
• Update existing migrations in examples
  • Add namespace property
  • Rename version to serial
• Update tests
  • Test single namespace migration (backward compat)
  • Test multi-namespace migration
  • Test cross-namespace dependencies
  • Test circular dependency detection
  • Test time-based rollback
  • Test dependency syntax parsing
  • Test error messages

No Migration Required

Important: This is a greenfields project with no existing dependencies or deployed databases. There is no need to migrate the __migrations table itself - the new schema will be used directly. Breaking changes to the migration system are acceptable at this stage.

Future Enhancements

1. Namespace isolation validation: Warn if migrations in different namespaces modify the same tables
2. Dependency versioning: Support range-based dependencies like "auth:>=2"
3. Migration status reporting: CLI-friendly output showing migration status across namespaces
4. Dry run mode: Show execution plan without applying changes