This script will look through your Schema and find any "foreign key" columns that are missing actual DB foreign keys. This could lead to orphaned rows and dirty data.

missing_fks.rb

# Parse and identify potential foreign key issues in a Rails schema.rb file.

# TO USE THIS, SAVE THE FILE AND RUN IT USING RAILS RUNNER. EXAMPLE:
# `rails runner missing_fks.rb`

def extract_table_names(schema_lines)
  schema_lines.select { |line| line.strip.start_with?('create_table') }
              .map { |line| line.split('"')[1] }
end

def extract_potential_foreign_keys(schema_lines)
  foreign_keys = {}
  current_table = nil

  schema_lines.each do |line|
    if line.strip.start_with?('create_table')
      current_table = line.split('"')[1]
      foreign_keys[current_table] = []
    elsif line.include?('_id') && current_table
      column_name = line.split('"')[1]
      foreign_keys[current_table] << column_name if column_name.end_with?('_id')
    end
  end

  foreign_keys
end

def check_foreign_keys(schema_lines, foreign_keys)
  missing_fk_constraints = {}

  foreign_keys.each do |table, keys|
    keys.each do |key|
    check1 = schema_lines.any? { |line| line.include?("add_foreign_key \"#{table}\",") && line.include?("\"#{key}\"") }
    check2 = schema_lines.any? { |line| line.include?("add_foreign_key \"#{table}\",") && line.include?("\"#{key[0..-4].pluralize}\"") }
      if !check1 && !check2
        missing_fk_constraints[table] ||= []
        missing_fk_constraints[table] << key
      end
    end
  end

  missing_fk_constraints
end

def main
  schema_file = 'db/schema.rb' # Replace with the path to your schema.rb file
  schema_lines = File.readlines(schema_file)

  tables = extract_table_names(schema_lines)
  potential_foreign_keys = extract_potential_foreign_keys(schema_lines)
  missing_foreign_key_constraints = check_foreign_keys(schema_lines, potential_foreign_keys)

  unless missing_foreign_key_constraints.empty?
    puts "Missing Foreign Key Constraints:"
    missing_foreign_key_constraints.each do |table, keys|
      puts "Table: #{table}, Keys: #{keys.join(', ')}"
    end
  else
    puts "No missing foreign key constraints detected."
  end
end

main