Skip to main content

Devlog Mar 1: Database & Authentication Improvements

· 6 min read
Eduardez
Eduardez
MoLOS Lead Developer

This week we made significant improvements to the database migration system and authentication infrastructure, focusing on better error handling, security enhancements, and improved migration reliability.

Database Migration Enhancements​

Migration Audit System​

We implemented a comprehensive migration audit system to track all migration operations:

// server/db/migrations/audit.ts
export interface MigrationAudit {
  migrationName: string;
  moduleName: string;
  appliedAt: Date;
  checksum: string;
  duration: number;
  success: boolean;
  error?: string;
}

export class MigrationAuditor {
  private audits: MigrationAudit[] = [];

  async recordMigration(
    migration: string,
    module: string,
    success: boolean,
    error?: string
  ): Promise<void> {
    const audit: MigrationAudit = {
      migrationName: migration,
      moduleName: module,
      appliedAt: new Date(),
      checksum: await this.calculateChecksum(migration),
      duration: performance.now(),
      success,
      error
    };

    this.audits.push(audit);
    await this.persistAudit(audit);
  }

  async validateMigrationIntegrity(): Promise<boolean> {
    for (const audit of this.audits) {
      const currentChecksum = await this.calculateChecksum(
        audit.migrationName
      );

      if (currentChecksum !== audit.checksum) {
        throw new MigrationIntegrityError(
          `Migration ${audit.migrationName} has been modified since application`
        );
      }
    }

    return true;
  }
}

The audit system provides:

  • Complete history of migration operations
  • Integrity verification of migration files
  • Performance tracking and analysis
  • Detailed error context for debugging

Duplicate Column Name Handling​

We fixed a critical issue with duplicate column names during migrations:

// server/db/migrations/handlers.ts
export async function handleDuplicateColumn(
  db: Database,
  tableName: string,
  columnName: string
): Promise<void> {
  try {
    // Check if column already exists
    const existingColumns = await db.query(`
      SELECT name FROM pragma_table_info('${tableName}')
    `);

    const hasColumn = existingColumns.some(
      (col: any) => col.name === columnName
    );

    if (hasColumn) {
      console.warn(
        `Column ${columnName} already exists in ${tableName}, skipping`
      );
      return;
    }

    // Apply column addition
    await db.exec(`
      ALTER TABLE ${tableName} ADD COLUMN ${columnName} TEXT
    `);
  } catch (error) {
    throw new MigrationError(
      `Failed to handle duplicate column ${columnName} in ${tableName}`,
      error
    );
  }
}

Migration Runner Rewrite​

We completely rewrote the migration runner to apply SQL directly:

// server/db/migrations/runner.ts
export class MigrationRunner {
  async run(migrationPath: string): Promise<MigrationResult> {
    const migrationFiles = await this.getMigrationFiles(migrationPath);
    const applied = new Set<string>();
    const failed: FailedMigration[] = [];

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

      try {
        const sql = await fs.readFile(file.path, 'utf-8');
        await this.db.exec(sql);

        await this.recordAppliedMigration(file);
        applied.add(file.name);

        console.log(`✓ Applied migration: ${file.name}`);
      } catch (error) {
        failed.push({
          name: file.name,
          error: error as Error
        });

        console.error(`✗ Failed migration: ${file.name}`, error);
      }
    }

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

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

    return result.length > 0;
  }
}

Authentication Improvements​

better-auth Update to 1.5.0​

We updated better-auth to version 1.5.0 with significant improvements:

// server/auth/index.ts
import { auth } from 'better-auth/server';
import { drizzleAdapter } from 'better-auth/adapters/drizzle';

const betterAuth = auth({
  adapter: drizzleAdapter(db, {
    user: users,
    session: sessions,
    account: accounts
  }),
  secret: process.env.BETTER_AUTH_SECRET,
  trustedOrigins: [
    process.env.ORIGIN || 'http://localhost:5173'
  ],
  plugins: [
    // Removed apiKey plugin for compatibility
  ],
  session: {
    expiresIn: 60 * 60 * 24 * 7 // 7 days
  }
});

Key changes:

  • Improved session management
  • Better error handling
  • Enhanced security features
  • Removed deprecated plugins

API Key Plugin Removal​

We removed the apiKey plugin for better-auth to improve compatibility:

// Before (removed)
import { apiKey } from 'better-auth/api-key';

const betterAuth = auth({
  // ...
  plugins: [
    apiKey({
      endpoint: '/api/auth/api-key'
    })
  ]
});

// After (removed apiKey)
const betterAuth = auth({
  // ...
  plugins: []
});

Reason for removal:

  • Better-auth API key plugin had compatibility issues
  • We implemented custom API key management for MCP
  • More control over authentication flow
  • Reduced dependency complexity

Authentication Flow Improvements​

We improved the overall authentication flow with better error handling:

// server/auth/flow.ts
export async function authenticateUser(
  credentials: UserCredentials
): Promise<AuthResult> {
  try {
    const session = await betterAuth.signIn.email({
      email: credentials.email,
      password: credentials.password
    });

    return {
      success: true,
      session: session.session,
      user: session.user
    };
  } catch (error) {
    if (error instanceof betterAuth.APIError) {
      switch (error.statusCode) {
        case 401:
          throw new AuthenticationError('Invalid credentials');
        case 404:
          throw new AuthenticationError('User not found');
        default:
          throw new AuthenticationError('Authentication failed');
      }
    }

    throw error;
  }
}

MCP Scope Enhancements​

3-Level Hierarchical Scoping​

We implemented a sophisticated 3-level hierarchical scoping system for MCP API keys:

// server/mcp/scopes.ts
export enum MCPScopeLevel {
  READ = 'read',
  WRITE = 'write',
  ADMIN = 'admin'
}

export interface MCPScope {
  module: string;
  level: MCPScopeLevel;
  resources?: string[];
}

export class MCPScopeManager {
  async validateScope(
    apiKey: string,
    requiredScope: MCPScope
  ): Promise<boolean> {
    const keyScopes = await this.getKeyScopes(apiKey);

    for (const scope of keyScopes) {
      if (scope.module !== requiredScope.module) {
        continue;
      }

      // Check if key has sufficient permission level
      const levelValue = (level: MCPScopeLevel) => {
        switch (level) {
          case MCPScopeLevel.READ:
            return 1;
          case MCPScopeLevel.WRITE:
            return 2;
          case MCPScopeLevel.ADMIN:
            return 3;
        }
      };

      if (levelValue(scope.level) >= levelValue(requiredScope.level)) {
        // Check resource-specific permissions if specified
        if (requiredScope.resources) {
          const hasResourceAccess = requiredScope.resources.every(
            resource => scope.resources?.includes(resource)
          );

          return hasResourceAccess;
        }

        return true;
      }
    }

    return false;
  }
}

The hierarchical scoping provides:

  • READ: Read-only access to module data
  • WRITE: Read and write access to module data
  • ADMIN: Full administrative access including configuration

Scope Normalization​

We added scope normalization to ensure consistent scope handling:

// server/mcp/normalizer.ts
export function normalizeScopes(scopes: string[]): MCPScope[] {
  const normalized: MCPScope[] = [];

  for (const scope of scopes) {
    // Parse scope format: "module:level" or "module:level:resource"
    const parts = scope.split(':');

    if (parts.length < 2) {
      continue;
    }

    const [module, level, ...resources] = parts;

    normalized.push({
      module,
      level: level as MCPScopeLevel,
      resources: resources.length > 0 ? resources : undefined
    });
  }

  return normalized;
}

Database Utilities​

Comprehensive Test Suite​

We added a comprehensive test suite for database operations:

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

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

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

  it('should apply pending migrations', async () => {
    const runner = new MigrationRunner(db);

    const result = await runner.run('./migrations');

    expect(result.applied).toContain('001_initial');
    expect(result.failed).toHaveLength(0);
  });

  it('should detect duplicate columns', async () => {
    await db.exec(`
      CREATE TABLE test_table (
        id INTEGER PRIMARY KEY,
        name TEXT
      )
    `);

    await handleDuplicateColumn(db, 'test_table', 'name');

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

    expect(columns).toHaveLength(2);
  });

  it('should validate migration integrity', async () => {
    const auditor = new MigrationAuditor(db);

    await auditor.recordMigration('001_initial', 'core', true);

    const isValid = await auditor.validateMigrationIntegrity();

    expect(isValid).toBe(true);
  });
});

Documentation Updates​

Migration Command References​

We updated all documentation to reflect the new migration commands:

# New migration commands
npm run db:migrate              # Run all pending migrations
npm run db:migrate:status       # Check migration status
npm run db:migrate:rollback     # Rollback last migration
npm run db:audit                # Audit module migrations

Duplicate Column Name Migration Fix​

We documented the duplicate column name handling in the migration documentation:

## Handling Duplicate Columns

The migration system now automatically handles duplicate column names during
migration operations. This prevents migration failures when columns already exist.

### Example

```sql
-- This migration will not fail if 'email' column already exists
ALTER TABLE users ADD COLUMN email TEXT;

The system will:

  1. Check if the column already exists
  2. Skip column addition if present
  3. Log a warning for auditing purposes

## Key Commits

- [refactor(db): rewrite migration runner to apply SQL directly](https://github.com/MoLOS-App/MoLOS/commit/588e726)
- [fix(db): handle duplicate column name during migration](https://github.com/MoLOS-App/MoLOS/commit/a378967)
- [docs: document duplicate column name migration fix](https://github.com/MoLOS-App/MoLOS/commit/ad820df)
- [fix(auth): remove apiKey plugin for compatibility](https://github.com/MoLOS-App/MoLOS/commit/0cb4168)
- [chore(deps): pin better-auth versions and remove agent config](https://github.com/MoLOS-App/MoLOS/commit/48289f5)
- [chore(deps): update better-auth to 1.5.0 and add better-call](https://github.com/MoLOS-App/MoLOS/commit/9f6a3a9)

## What's Next

The database and authentication improvements enhance the reliability and security of the platform. Coming up:

- Implementing database connection pooling
- Adding migration preview/dry-run functionality
- Enhancing authentication with multi-factor support
- Implementing API key rotation policies
- Adding database performance monitoring

---

*These database and authentication improvements provide a more robust and secure foundation for the MoLOS platform.*