1 files changed, 117 insertions, 0 deletions
diff --git a/scripts/migration_schema_validator.rb b/scripts/migration_schema_validator.rb
new file mode 100644
index 00000000000..08b904ce46c
--- /dev/null
+++ b/scripts/migration_schema_validator.rb
@@ -0,0 +1,117 @@
+# frozen_string_literal: true
+
+require 'open3'
+
+class MigrationSchemaValidator
+  FILENAME = 'db/structure.sql'
+
+  MIGRATION_DIRS = %w[db/migrate db/post_migrate].freeze
+
+  SCHEMA_VERSION_DIR = 'db/schema_migrations'
+
+  VERSION_DIGITS = 14
+
+  def validate!
+    if committed_migrations.empty?
+      puts "\e[32m No migrations found, skipping schema validation\e[0m"
+      return
+    end
+
+    validate_schema_on_rollback!
+    validate_schema_on_migrate!
+    validate_schema_version_files!
+  end
+
+  private
+
+  def validate_schema_on_rollback!
+    committed_migrations.reverse_each do |filename|
+      version = find_migration_version(filename)
+
+      run("scripts/db_tasks db:migrate:down VERSION=#{version}")
+      run("scripts/db_tasks db:schema:dump")
+    end
+
+    git_command = "git diff #{diff_target} -- #{FILENAME}"
+    base_message = "rollback of added migrations does not revert #{FILENAME} to previous state"
+
+    validate_clean_output!(git_command, base_message)
+  end
+
+  def validate_schema_on_migrate!
+    run("scripts/db_tasks db:migrate")
+    run("scripts/db_tasks db:schema:dump")
+
+    git_command = "git diff -- #{FILENAME}"
+    base_message = "the committed #{FILENAME} does not match the one generated by running added migrations"
+
+    validate_clean_output!(git_command, base_message)
+  end
+
+  def validate_schema_version_files!
+    git_command = "git add -A -n #{SCHEMA_VERSION_DIR}"
+    base_message = "the committed files in #{SCHEMA_VERSION_DIR} do not match those expected by the added migrations"
+
+    validate_clean_output!(git_command, base_message)
+  end
+
+  def committed_migrations
+    @committed_migrations ||= begin
+      git_command = "git diff --name-only --diff-filter=A #{diff_target} -- #{MIGRATION_DIRS.join(' ')}"
+
+      run(git_command).split("\n")
+    end
+  end
+
+  def diff_target
+    @diff_target ||= pipeline_for_merged_results? ? target_branch : merge_base
+  end
+
+  def merge_base
+    run("git merge-base #{target_branch} #{source_ref}")
+  end
+
+  def target_branch
+    ENV['CI_MERGE_REQUEST_TARGET_BRANCH_NAME'] || ENV['TARGET'] || ENV['CI_DEFAULT_BRANCH'] || 'master'
+  end
+
+  def source_ref
+    ENV['CI_COMMIT_SHA'] || 'HEAD'
+  end
+
+  def pipeline_for_merged_results?
+    ENV.key?('CI_MERGE_REQUEST_SOURCE_BRANCH_SHA')
+  end
+
+  def find_migration_version(filename)
+    file_basename = File.basename(filename)
+    version_match = /\A(?<version>\d{#{VERSION_DIGITS}})_/o.match(file_basename)
+
+    die "#{filename} has an invalid migration version" if version_match.nil?
+
+    version_match[:version]
+  end
+
+  def validate_clean_output!(command, base_message)
+    command_output = run(command)
+
+    return if command_output.empty?
+
+    die "#{base_message}:\n#{command_output}"
+  end
+
+  def die(message, error_code: 1)
+    puts "\e[31mError: #{message}\e[0m"
+    exit error_code
+  end
+
+  def run(cmd)
+    puts "\e[32m$ #{cmd}\e[37m"
+    stdout_str, stderr_str, status = Open3.capture3(cmd)
+    puts "#{stdout_str}#{stderr_str}\e[0m"
+
+    die "command failed: #{stderr_str}" unless status.success?
+
+    stdout_str.chomp
+  end
+end