feat: debug schema

server/src/commands/debug-migrations.command.ts

@@ -0,0 +1,82 @@
+import { Command, CommandRunner } from 'nest-commander';
+import { CliService } from 'src/services/cli.service';
+
+@Command({
+  name: 'debug-migrations',
+  description: 'Run a report to debug issues with database migrations',
+})
+export class DebugMigrations extends CommandRunner {
+  constructor(private service: CliService) {
+    super();
+  }
+
+  async run(): Promise<void> {
+    try {
+      const report = await this.service.debugMigrations();
+
+      const maxLength = Math.max(...report.results.map((item) => item.name.length));
+
+      const success = report.results.filter((item) => item.status === 'applied');
+      const deleted = report.results.filter((item) => item.status === 'deleted');
+      const missing = report.results.filter((item) => item.status === 'missing');
+
+      for (const item of report.results) {
+        const name = item.name.padEnd(maxLength, ' ');
+        switch (item.status) {
+          case 'applied': {
+            console.log(`✅ ${name}`);
+            break;
+          }
+
+          case 'deleted': {
+            console.log(`❌ ${name} - Deleted! (this migration does not exist anymore)`);
+            break;
+          }
+
+          case 'missing': {
+            console.log(`⚠️ ${name} - Missing! (this migration needs to be applied still)`);
+            break;
+          }
+        }
+      }
+
+      if (missing.length === 0 && deleted.length === 0) {
+        console.log(`\nAll ${success.length} migrations have been successfully applied! 🎉`);
+      } else {
+        console.log(`\nMigration issues detected:`);
+        console.log(`  Missing migrations: ${missing.length}`);
+        console.log(`  Deleted migrations: ${deleted.length}`);
+        console.log(`  Successfully applied migrations: ${success.length}`);
+      }
+    } catch (error) {
+      console.error(error);
+      console.error('Unable to debug migrations');
+    }
+  }
+}
+
+@Command({
+  name: 'debug-schema',
+  description: 'Run a report to debug issues with database schema',
+})
+export class DebugSchema extends CommandRunner {
+  constructor(private service: CliService) {
+    super();
+  }
+
+  async run(): Promise<void> {
+    try {
+      const output = await this.service.debugSchema();
+
+      if (output.length === 0) {
+        console.log('No schema changes detected');
+        return;
+      }
+
+      console.log(output.join('\n'));
+    } catch (error) {
+      console.error(error);
+      console.error('Unable to debug schema');
+    }
+  }
+}

server/src/commands/index.ts

@@ -1,3 +1,4 @@
+import { DebugMigrations, DebugSchema } from 'src/commands/debug-migrations.command';
 import { GrantAdminCommand, PromptEmailQuestion, RevokeAdminCommand } from 'src/commands/grant-admin';
 import { ListUsersCommand } from 'src/commands/list-users.command';
 import { DisableMaintenanceModeCommand, EnableMaintenanceModeCommand } from 'src/commands/maintenance-mode';
@@ -28,4 +29,6 @@ export const commandsAndQuestions = [
   ChangeMediaLocationCommand,
   PromptMediaLocationQuestions,
   PromptConfirmMoveQuestions,
+  DebugMigrations,
+  DebugSchema,
 ];

server/src/repositories/database.repository.ts

@@ -281,6 +281,14 @@ export class DatabaseRepository {
     return rows[0].db;
   }
 
+  async getMigrations() {
+    const { rows } = await sql<{
+      name: string;
+      timestamp: string;
+    }>`SELECT * FROM kysely_migrations ORDER BY name ASC`.execute(this.db);
+    return rows;
+  }
+
   async getDimensionSize(table: string, column = 'embedding'): Promise<number> {
     const { rows } = await sql<{ dimsize: number }>`
       SELECT atttypmod as dimsize

server/src/services/cli.service.ts

@@ -1,15 +1,80 @@
 import { Injectable } from '@nestjs/common';
-import { isAbsolute } from 'node:path';
+import { isAbsolute, join } from 'node:path';
+import postgres from 'postgres';
 import { SALT_ROUNDS } from 'src/constants';
 import { MaintenanceAuthDto } from 'src/dtos/maintenance.dto';
 import { UserAdminResponseDto, mapUserAdmin } from 'src/dtos/user.dto';
 import { MaintenanceAction, SystemMetadataKey } from 'src/enum';
 import { BaseService } from 'src/services/base.service';
+import { schemaDiff, schemaFromCode, schemaFromDatabase } from 'src/sql-tools';
+import { asPostgresConnectionConfig } from 'src/utils/database';
 import { createMaintenanceLoginUrl, generateMaintenanceSecret } from 'src/utils/maintenance';
 import { getExternalDomain } from 'src/utils/misc';
 
+export type MigrationReport = {
+  files: string[];
+  rows: Array<{ name: string; timestamp: string }>;
+  results: MigrationStatus[];
+};
+type MigrationStatus = {
+  name: string;
+  status: 'applied' | 'missing' | 'deleted';
+};
+
 @Injectable()
 export class CliService extends BaseService {
+  async debugMigrations(): Promise<MigrationReport> {
+    // eslint-disable-next-line unicorn/prefer-module
+    const allFiles = await this.storageRepository.readdir(join(__dirname, '../schema/migrations'));
+    const files = allFiles.filter((file) => file.endsWith('.js')).map((file) => file.slice(0, -3));
+    const rows = await this.databaseRepository.getMigrations();
+    const filesSet = new Set(files);
+    const rowsSet = new Set(rows.map((item) => item.name));
+    const combined = [...filesSet, ...rowsSet].toSorted();
+
+    const results: MigrationStatus[] = [];
+
+    for (const name of combined) {
+      if (filesSet.has(name) && rowsSet.has(name)) {
+        results.push({ name, status: 'applied' });
+        continue;
+      }
+
+      if (filesSet.has(name) && !rowsSet.has(name)) {
+        results.push({ name, status: 'missing' });
+        continue;
+      }
+
+      if (!filesSet.has(name) && rowsSet.has(name)) {
+        results.push({ name, status: 'deleted' });
+        continue;
+      }
+    }
+
+    return { files, rows, results };
+  }
+
+  async debugSchema() {
+    const source = schemaFromCode({ overrides: true, namingStrategy: 'default' });
+    const { database } = this.configRepository.getEnv();
+    const db = postgres(asPostgresConnectionConfig(database.config));
+    const target = await schemaFromDatabase(db, {});
+
+    console.log(source.warnings.join('\n'));
+
+    const up = schemaDiff(source, target, {
+      tables: { ignoreExtra: true },
+      functions: { ignoreExtra: false },
+      parameters: { ignoreExtra: true },
+    });
+
+    if (up.items.length === 0) {
+      return [];
+    }
+
+    return up.asSql();
+  }
+
   async listUsers(): Promise<UserAdminResponseDto[]> {
     const users = await this.userRepository.getList({ withDeleted: true });
     return users.map((user) => mapUserAdmin(user));