Our rails db includes our own tables/schema and the Salesforce/Heroku Connect schema (under "salesforce.*"). We place this file in config/initializers/schema_dumper.rb and now our rails db:schema:dump includes both our own tables and the salesforce. tables.

schema_dumper.rb

# This solution was based on https://gist.github.com/GlenCrawford/16163abab7852c1bd550547f29971c18

Rails.configuration.to_prepare do
  ActiveRecord::SchemaDumper.ignore_tables = %w[
    salesforce._hcmeta
    salesforce._sf_event_log
    salesforce._trigger_log
    salesforce._trigger_log_archive
  ]
end

class ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaDumper
  # Overridden in order to call new method "schemas".
  def dump(stream)
    @options[:table_name_prefix] = "public."
    header(stream)
    extensions(stream)
    types(stream)
    schemas(stream)
    tables(stream)
    trailer(stream)

    stream
  end

  private

  # Adds following lines just after the extensions:
  # * connection.execute "CREATE SCHEMA ..."
  # * connection.schema_search_path = ...
  def schemas(stream)
    @connection.schema_search_path.split(",").each do |name|
      stream.puts %(  connection.execute "CREATE SCHEMA IF NOT EXISTS #{name}")
    end
    stream.puts ""
    stream.puts %(  connection.schema_search_path = #{@connection.schema_search_path.inspect})
    stream.puts ""
  end

  # Overridden in order to build a list of tables with their schema prefix
  # (rest of the method is the same).
  def tables(stream)
    table_query = <<-SQL
          SELECT schemaname, tablename
          FROM pg_tables
          WHERE schemaname = ANY(current_schemas(false))
    SQL

    sorted_tables = @connection.exec_query(table_query, "SCHEMA").map do |table|
      "#{table["schemaname"]}.#{table["tablename"]}"
    end.sort

    sorted_tables.each do |table_name|
      table(table_name, stream) unless ignored?(table_name)
    end

    if @connection.supports_foreign_keys?
      sorted_tables.each do |tbl|
        foreign_keys(tbl, stream) unless ignored?(tbl)
      end
    end
  end

  # We do not want the auto-generated default for salesforce.* tables
  # For our own UUID tables we generate the ID with:
  #   default: -> { "uuid_generate_v4()" }
  # And the salesforce tables were producing the following default but not providing the sequence:
  #   default: -> { "nextval('contact_id_seq'::regclass)" }
  #
  # So, if the default contains `nextval` then we remove it from our schema.rb.
  #
  # Hopefully this doesn't bite us one day.
  def column_spec_for_primary_key(column)
    spec = super
    spec.delete(:default) if /nextval/.match?(spec[:default])
    spec
  end
end

In looking options to handle this currently, I can see Rails 7.1 seems to have some logic for handling create_schema invocations: https://github.com/rails/rails/blob/7-1-stable/activerecord/lib/active_record/connection_adapters/postgresql/schema_dumper.rb#L31 unlike 7.0's branch.