Devlog Mar 8: Database & Migration Refactoring

This week we undertook a major refactoring of the database and migration system, consolidating schemas into a dedicated @molos/database package, separating module scopes from permission scopes, and rewriting the migration runner for improved reliability and maintainability.

@molos/database Package Migration

Package Structure

We migrated all database schemas to a dedicated @molos/database package:

packages/@molos/database/
├── src/
│   ├── schema/
│   │   ├── index.ts          # Main schema export
│   │   ├── core.ts           # Core tables (users, sessions)
│   │   ├── modules.ts        # Module tables (module_config)
│   │   ├── oauth.ts          # OAuth tables
│   │   └── mcp.ts            # MCP tables
│   ├── migrations/
│   │   ├── 001_initial.sql
│   │   ├── 002_oauth.sql
│   │   └── 003_mcp.sql
│   └── index.ts
├── package.json
└── drizzle.config.ts

Schema Consolidation

The new package consolidates all database schemas:

// packages/@molos/database/src/schema/index.ts
export * from './core';
export * from './modules';
export * from './oauth';
export * from './mcp';

export const schema = {
  ...coreSchema,
  ...modulesSchema,
  ...oauthSchema,
  ...mcpSchema
};

Core Schema

Core application tables:

// packages/@molos/database/src/schema/core.ts
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  email: text('email').notNull().unique(),
  password: text('password').notNull(),
  name: text('name'),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull()
});

export const sessions = sqliteTable('sessions', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull().references(() => users.id),
  token: text('token').notNull().unique(),
  expiresAt: integer('expires_at', { mode: 'timestamp' }).notNull(),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull()
});

Module Schema

Module configuration and management:

// packages/@molos/database/src/schema/modules.ts
import { sqliteTable, text, integer, json } from 'drizzle-orm/sqlite-core';

export const modules = sqliteTable('modules', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  version: text('version').notNull(),
  enabled: integer('enabled', { mode: 'boolean' }).notNull().default(true),
  config: json('config').$type<Record<string, any>>(),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull()
});

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

Scope Separation

Module Scopes vs Permission Scopes

We separated module scopes from permission scopes for better security:

// packages/@molos/database/src/schema/mcp.ts
import { sqliteTable, text, integer, json } from 'drizzle-orm/sqlite-core';

// Module scopes: define which modules an API key can access
export const mcpModuleScopes = sqliteTable('mcp_module_scopes', {
  id: text('id').primaryKey(),
  apiKeyId: text('api_key_id').notNull().references(() => mcpApiKeys.id),
  moduleName: text('module_name').notNull(),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull()
});

// Permission scopes: define what actions can be performed
export const mcpPermissionScopes = sqliteTable('mcp_permission_scopes', {
  id: text('id').primaryKey(),
  apiKeyId: text('api_key_id').notNull().references(() => mcpApiKeys.id),
  resource: text('resource').notNull(),
  level: text('level').notNull(), // read, write, admin
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull()
});

export const mcpApiKeys = sqliteTable('mcp_api_keys', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  keyHash: text('key_hash').notNull(),
  expiresAt: integer('expires_at', { mode: 'timestamp' }),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull()
});

Scope Validation

New validation logic for scope checking:

// server/mcp/scopes.ts
export class ScopeValidator {
  async validateAccess(
    apiKey: string,
    module: string,
    permission: string
  ): Promise<boolean> {
    const key = await this.getApiKey(apiKey);

    // Check module scope
    const hasModuleAccess = await this.db.query.mcpModuleScopes.findFirst({
      where: and(
        eq(mcpModuleScopes.apiKeyId, key.id),
        eq(mcpModuleScopes.moduleName, module)
      )
    });

    if (!hasModuleAccess) {
      return false;
    }

    // Check permission scope
    const hasPermission = await this.db.query.mcpPermissionScopes.findFirst({
      where: and(
        eq(mcpPermissionScopes.apiKeyId, key.id),
        eq(mcpPermissionScopes.resource, permission)
      )
    });

    if (!hasPermission) {
      return false;
    }

    return true;
  }
}

Migration System Rewrite

Unified Migration Runner

We rewrote the migration runner for better error handling and performance:

// packages/@molos/database/src/migrations/runner.ts
export interface MigrationFile {
  name: string;
  path: string;
  checksum: string;
}

export class MigrationRunner {
  constructor(private db: Database) {}

  async run(migrationsPath: string): Promise<MigrationResult> {
    const migrationFiles = await this.getMigrationFiles(migrationsPath);
    const applied: string[] = [];
    const failed: FailedMigration[] = [];

    for (const file of migrationFiles) {
      if (await this.isApplied(file.name)) {
        continue;
      }

      try {
        await this.applyMigration(file);
        applied.push(file.name);
        console.log(`✓ Applied: ${file.name}`);
      } catch (error) {
        failed.push({
          name: file.name,
          error: error as Error
        });
        console.error(`✗ Failed: ${file.name}`, error);
      }
    }

    return {
      applied,
      failed,
      timestamp: new Date()
    };
  }

  private async applyMigration(file: MigrationFile): Promise<void> {
    const sql = await fs.readFile(file.path, 'utf-8');

    // Begin transaction
    await this.db.run('BEGIN TRANSACTION');

    try {
      // Apply SQL
      await this.db.exec(sql);

      // Record migration
      await this.db.run(`
        INSERT INTO __drizzle_migrations (hash, created_at)
        VALUES (?, ?)
      `, [file.name, Date.now()]);

      // Commit transaction
      await this.db.run('COMMIT');
    } catch (error) {
      // Rollback on error
      await this.db.run('ROLLBACK');
      throw new MigrationError(
        `Failed to apply migration ${file.name}`,
        error
      );
    }
  }

  private async isApplied(name: string): Promise<boolean> {
    const result = await this.db.get(`
      SELECT 1 FROM __drizzle_migrations WHERE hash = ?
    `, [name]);

    return !!result;
  }

  private async getMigrationFiles(
    path: string
  ): Promise<MigrationFile[]> {
    const files = await fs.readdir(path);
    const sqlFiles = files
      .filter(f => f.endsWith('.sql'))
      .sort()
      .map(file => ({
        name: file,
        path: join(path, file),
        checksum: this.calculateChecksum(join(path, file))
      }));

    return sqlFiles;
  }

  private async calculateChecksum(filePath: string): Promise<string> {
    const content = await fs.readFile(filePath, 'utf-8');
    return createHash('sha256').update(content).digest('hex');
  }
}

Enhanced Error Handling

We added comprehensive error handling for column rename operations:

// packages/@molos/database/src/migrations/handlers.ts
export async function handleColumnRename(
  db: Database,
  table: string,
  oldName: string,
  newName: string
): Promise<void> {
  try {
    // SQLite doesn't support ALTER COLUMN directly
    // We need to recreate the table

    await db.run('BEGIN TRANSACTION');

    // Get original table schema
    const schema = await db.query(`
      SELECT sql FROM sqlite_master WHERE type='table' AND name=?
    `, [table]);

    // Create new table with renamed column
    const newSchema = schema[0].sql.replace(
      new RegExp(`\\b${oldName}\\b`, 'g'),
      newName
    );

    await db.exec(`DROP TABLE IF EXISTS ${table}_new`);
    await db.exec(newSchema.replace(`CREATE TABLE ${table}`, `CREATE TABLE ${table}_new`));

    // Copy data
    await db.exec(`
      INSERT INTO ${table}_new SELECT * FROM ${table}
    `);

    // Drop old table and rename new one
    await db.exec(`DROP TABLE ${table}`);
    await db.exec(`ALTER TABLE ${table}_new RENAME TO ${table}`);

    await db.run('COMMIT');
  } catch (error) {
    await db.run('ROLLBACK');
    throw new MigrationError(
      `Failed to rename column ${oldName} to ${newName} in table ${table}`,
      error
    );
  }
}

Migration Runner Consolidation

Renamed Command

We simplified the migration command from db:migrate:unified to db:migrate:

// package.json
{
  "scripts": {
    "db:migrate": "tsx server/db/migrations/index.ts",
    "db:migrate:status": "tsx server/db/migrations/status.ts",
    "db:migrate:rollback": "tsx server/db/migrations/rollback.ts"
  }
}

This change:

• Simplifies the user interface
• Makes commands more intuitive
• Reduces typing and confusion
• Aligns with common CLI conventions

Comprehensive Testing

We added comprehensive tests for the new migration system:

// tests/database/migrations.test.ts
describe('Migration System', () => {
  let db: Database;
  let runner: MigrationRunner;

  beforeEach(async () => {
    db = await createTestDatabase();
    runner = new MigrationRunner(db);
  });

  afterEach(async () => {
    await db.close();
  });

  it('should apply migrations in order', async () => {
    const result = await runner.run('./test/migrations');

    expect(result.applied).toHaveLength(3);
    expect(result.failed).toHaveLength(0);
  });

  it('should skip already applied migrations', async () => {
    await runner.run('./test/migrations');
    const result = await runner.run('./test/migrations');

    expect(result.applied).toHaveLength(0);
  });

  it('should rollback failed transactions', async () => {
    const badMigration = './test/migrations/004_bad.sql';

    await expect(
      runner.run('./test/migrations')
    ).rejects.toThrow(MigrationError);

    const tables = await db.query(`
      SELECT name FROM sqlite_master WHERE type='table'
    `);

    // Bad migration table should not exist
    expect(tables).not.toContainEqual(
      expect.objectContaining({ name: 'bad_table' })
    );
  });

  it('should handle column renames correctly', async () => {
    await handleColumnRename(db, 'users', 'email', 'username');

    const columns = await db.query(`
      SELECT name FROM pragma_table_info('users')
    `);

    expect(columns).toContainEqual(expect.objectContaining({ name: 'username' }));
    expect(columns).not.toContainEqual(expect.objectContaining({ name: 'email' }));
  });
});

Key Commits

• refactor(database): consolidate schema and rewrite migration system
• refactor(database): migrate schema to @molos/database package and separate module scopes from permission scopes
• fix(database): improve migration error handling for column rename operations
• refactor(database): consolidate migration system and improve error handling
• feat(db): implement production-ready migration system v2.1
• Renames the unified migration runner command from db:migrate:unified to db:migrate
• refactor(db): rename allowed_modules to allowed_scopes

What's Next

The database refactoring provides a cleaner, more maintainable architecture. Coming up:

• Adding migration rollback support
• Implementing migration preview/dry-run functionality
• Adding database performance monitoring
• Implementing connection pooling
• Supporting PostgreSQL alongside SQLite

---

This database and migration refactoring significantly improves the reliability and maintainability of our data layer.