Devlog Feb 12: Database Migration System v2

This week marks a major milestone in MoLOS's database infrastructure with the launch of Database Migration System v2. After weeks of planning and implementation documented in ADR-001, we've deployed a comprehensive migration system that brings enterprise-grade reliability to our database operations.

Overview of Migration System v2

The new migration system addresses several critical issues from our previous approach:

• Unified migration tracking: Single source of truth for core and module migrations
• Schema validation: Automatic detection of drift between expected and actual database state
• Enhanced rollback: Support for manual .down.sql files alongside automated rollback
• Migration health monitoring: Real-time health API endpoint
• Audit support: Comprehensive logging of all migration operations
• Backup automation: Automatic database backups before schema modifications

Implementation Details

Unified Migration Runner

The core of the new system is the unified migration runner that handles both core and module migrations:

export async function runMigrations(options: MigrationOptions): Promise<MigrationResult> {
  const logger = new MigrationLogger(options);
  const validator = new SchemaValidator(options);

  logger.info('Starting unified migration run');

  // 1. Validate current state
  const validationResult = await validator.validate();
  if (!validationResult.valid) {
    throw new MigrationError('Schema validation failed', validationResult.errors);
  }

  // 2. Run core migrations
  const coreMigrations = await getCoreMigrations();
  await runMigrationSet(coreMigrations, 'core', logger);

  // 3. Run module migrations (namespaced)
  const modules = await getInstalledModules();
  for (const module of modules) {
    const moduleMigrations = await getModuleMigrations(module.name);
    await runMigrationSet(moduleMigrations, module.name, logger);
  }

  logger.info('Migration run completed successfully');
  return { success: true, applied: logger.getAppliedMigrations() };
}

Schema Validation

We've implemented a comprehensive schema validator that compares the actual database state against expected schemas:

export class SchemaValidator {
  async validate(): Promise<ValidationResult> {
    const actualSchema = await this.extractActualSchema();
    const expectedSchema = await this.loadExpectedSchema();

    const errors: SchemaError[] = [];

    // Check for missing tables
    for (const [tableName, tableSchema] of Object.entries(expectedSchema)) {
      if (!actualSchema[tableName]) {
        errors.push({
          type: 'missing_table',
          table: tableName,
          message: `Table '${tableName}' does not exist in database`
        });
      } else {
        // Check column mismatches
        const columnErrors = this.compareColumns(
          expectedSchema[tableName],
          actualSchema[tableName]
        );
        errors.push(...columnErrors);
      }
    }

    return {
      valid: errors.length === 0,
      errors
    };
  }
}

Migration Tracking

We've deprecated the custom coreModuleMigrations table in favor of Drizzle's native tracking system, as outlined in ADR-001. This provides better reliability and compatibility with Drizzle's ecosystem.

The new migration table structure:

export const coreModuleMigrations = sqliteTable('core_module_migrations', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  moduleName: text('module_name').notNull(),
  migrationName: text('migration_name').notNull(),
  appliedAt: integer('applied_at').notNull(),
  checksum: text('checksum').notNull()
});

Enhanced Error Handling

Error handling has been significantly improved with detailed error messages and context:

export class MigrationError extends Error {
  constructor(
    message: string,
    public readonly errors: SchemaError[],
    public readonly context?: MigrationContext
  ) {
    super(message);
    this.name = 'MigrationError';
  }

  toJSON() {
    return {
      message: this.message,
      errors: this.errors,
      context: this.context
    };
  }
}

Migration Audit System

We've added a comprehensive audit system to track module migrations and ensure consistency:

# Audit module migrations
npm run db:audit-modules

The audit script validates:

• Module migration synchronization with core system
• Table prefix compliance
• Missing or duplicate migrations
• Migration checksum integrity

Automatic Backups

Database backups are now automatically created before any schema modification:

export async function createBackup(): Promise<string> {
  const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
  const backupPath = path.join(BACKUP_DIR, `backup-${timestamp}.db`);

  await fs.copyFile(DATABASE_PATH, backupPath);
  console.log(`Database backed up to: ${backupPath}`);

  return backupPath;
}

Testing Coverage

We've implemented a comprehensive test suite covering all aspects of the migration system:

• Migration execution tests: Verify migrations run correctly and in order
• Rollback tests: Ensure .down.sql files work as expected
• Validation tests: Test schema drift detection
• Namespace tests: Verify module table prefixing
• Error handling tests: Validate error scenarios

describe('Migration System', () => {
  it('should run all pending migrations', async () => {
    const result = await runMigrations({ database: testDb });
    expect(result.success).toBe(true);
    expect(result.applied.length).toBeGreaterThan(0);
  });

  it('should detect schema drift', async () => {
    // Introduce drift
    await testDb.run(`
      ALTER TABLE users ADD COLUMN extra_column TEXT
    `);

    const validator = new SchemaValidator({ database: testDb });
    const result = await validator.validate();

    expect(result.valid).toBe(false);
    expect(result.errors).toContainEqual(
      expect.objectContaining({
        type: 'extra_column'
      })
    );
  });
});

Migration Health API

We've added a health endpoint for monitoring migration status:

// GET /api/health/migrations
export async function GET() {
  const status = await getMigrationHealth();

  return json({
    status: status.healthy ? 'healthy' : 'unhealthy',
    migrations: {
      total: status.total,
      applied: status.applied,
      pending: status.pending,
      failed: status.failed
    },
    lastMigration: status.lastMigration,
    schemaValid: status.schemaValid
  });
}

Documentation Updates

We've significantly expanded our documentation to cover the new migration system:

• ADR-001: Migration Tracking Strategy - comprehensive decision record
• Database Architecture: Complete system overview
• Testing Guide: How to test migrations
• Troubleshooting: Common issues and solutions

Key Commits

• feat(db): add unified migration system with validation and observability - Core migration system implementation
• Add migration audit, backup support, and enhanced enum validation - Audit and backup features
• feat(database): add migration tracking and validation enhancements - Migration tracking enhancements

What's Next

The migration system v2 provides a solid foundation for database operations. In the coming weeks, we'll be:

• Implementing parallel migration execution for faster module initialization
• Adding migration preview/dry-run functionality
• Developing a migration dashboard for visual monitoring
• Enhancing CI/CD integration with automated migration testing
• Supporting PostgreSQL alongside SQLite for production deployments