Consolidate Database Migrations into Single File

Description

The project currently has 8 separate migration files in src/database/migrations/. This creates unnecessary complexity for fresh database setups and makes it harder to maintain the initial schema state. We should consolidate all migrations into a single comprehensive migration file.

Current State

The following migrations exist:

src/database/migrations/
├── 1755604729706-InitSchemas.ts
├── 1755614062187-AddUserProfile.ts
├── 1755615000000-AddBronzeUsersAndRepos.ts
├── 1755616000000-AddGoldRepository.ts
├── 1755617000000-AddProcessingStatusToUsers.ts
├── 1755618000000-AddProcessingQueue.ts
├── 1755620000000-AddMissingFields.ts
└── 1755625000000-AddOwnerUserIdToRepository.ts

Problems with Current Approach

• Complexity: New developers need to run 8 migrations to set up a fresh database
• Maintenance: Multiple files make it harder to understand the complete schema
• Testing: Integration tests need to run all migrations sequentially
• Deployment: Fresh deployments (new environments) require applying all historical migrations
• Risk: More files = more chances for migration conflicts or errors

Proposed Solution

Create a single consolidated migration file that:

1. Contains all schema definitions from the 8 existing migrations
2. Creates all tables, indexes, and constraints in one go
3. Represents the current complete state of the database
4. Uses a new timestamp to ensure it runs after any existing migrations in production

Tasks

• Review all 8 migration files and document their changes
• Create a new consolidated migration file (e.g., 1730000000000-ConsolidatedSchema.ts)
• Include all tables, columns, indexes, and constraints from existing migrations
• Test the consolidated migration on a fresh database
• Verify that entities match the new migration schema
• Update documentation with the new migration approach
• For existing databases: Keep old migrations for backward compatibility
• For new databases: Only the consolidated migration will run

Implementation Notes

// Example structure for consolidated migration
export class ConsolidatedSchema1730000000000 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // Create all tables, indexes, and constraints
    // Based on current schema from all 8 migrations
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    // Drop everything in reverse order
  }
}

Migration Strategy

1. DO NOT DELETE old migration files immediately (for existing databases)
2. The consolidated migration should have a timestamp newer than all existing ones
3. Existing databases will skip it (already migrated)
4. New databases will only run the consolidated migration
5. After confirming all environments are stable, old migrations can be archived

Benefits

• ✅ Simpler onboarding for new developers
• ✅ Faster fresh database setup
• ✅ Easier to understand complete schema
• ✅ Reduced migration execution time for new environments
• ✅ Single source of truth for database structure

Related Files

• src/database/data-source.ts - Migration configuration
• src/database/migrations/*.ts - Current migration files
• All entity files in src/raw/**/*.entity.ts and src/analytics/**/*.entity.ts

Priority

Medium - This is a maintenance improvement that will benefit long-term project health

References

• TypeORM Migrations Documentation
• Database Migration Best Practices