# 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:

```vala
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:

```sql
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

```mermaid
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.

```mermaid
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:

```vala
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):

```vala
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:

```vala
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

```vala
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