fix: force build_contact and increment_view_count methods to use writing role

These methods are used by the widget to create a contact and helpcenter to
increment the view count. Since these methods are triggered by GET
request, Rails will use the reading role which will fail if the table
is readonly.

.env.example

@@ -57,6 +57,13 @@ POSTGRES_PASSWORD=
 RAILS_ENV=development
 # Changes the Postgres query timeout limit. The default is 14 seconds. Modify only when required.
 # POSTGRES_STATEMENT_TIMEOUT=14s
+
+# read replica configuration for production
+# POSTGRES_HOST_RR=postgres_rr
+# create a new user with readonly permissions
+# POSTGRES_USERNAME_RR=postgres_readonly
+# POSTGRES_PASSWORD_RR=
+
 RAILS_MAX_THREADS=5
 
 # The email from which all outgoing emails are sent

.github/workflows/run_foss_spec.yml

@@ -45,7 +45,6 @@ jobs:
       with:
         ref: ${{ github.event.pull_request.head.ref }}
         repository: ${{ github.event.pull_request.head.repo.full_name }}
-
     - uses: ruby/setup-ruby@v1
       with:
         bundler-cache: true # runs 'bundle install' and caches installed gems automatically

app/controllers/widgets_controller.rb

@@ -47,8 +47,10 @@ class WidgetsController < ActionController::Base
   def build_contact
     return if @contact.present?
 
-    @contact_inbox, @token = build_contact_inbox_with_token(@web_widget, additional_attributes)
-    @contact = @contact_inbox.contact
+    ActiveRecord::Base.connected_to(role: :writing) do
+      @contact_inbox, @token = build_contact_inbox_with_token(@web_widget, additional_attributes)
+      @contact = @contact_inbox.contact
+    end
   end
 
   def ensure_account_is_active

app/models/application_record.rb

@@ -2,6 +2,8 @@ class ApplicationRecord < ActiveRecord::Base
   include Events::Types
   self.abstract_class = true
 
+  connects_to database: { writing: :primary, reading: :primary_replica }
+
   before_validation :validates_column_content_length
 
   # the models that exposed in email templates through liquid

app/models/article.rb

@@ -114,8 +114,10 @@ class Article < ApplicationRecord
 
   def increment_view_count
     # rubocop:disable Rails/SkipsModelValidations
-    update_column(:views, views? ? views + 1 : 1)
-    # rubocop:enable Rails/SkipsModelValidations
+    ActiveRecord::Base.connected_to(role: :writing) do
+      update_column(:views, views? ? views + 1 : 1)
+      # rubocop:enable Rails/SkipsModelValidations
+    end
   end
 
   def self.update_positions(positions_hash)

config/database.yml

@@ -10,19 +10,41 @@ default: &default
     statement_timeout: <%= ENV["POSTGRES_STATEMENT_TIMEOUT"] || "14s" %>
 
 development:
-  <<: *default
-  database: "<%= ENV.fetch('POSTGRES_DATABASE', 'chatwoot_dev') %>"
-  username: "<%= ENV.fetch('POSTGRES_USERNAME', 'postgres') %>"
-  password: "<%= ENV.fetch('POSTGRES_PASSWORD', '') %>"
+  primary:
+    <<: *default
+    database: "<%= ENV.fetch('POSTGRES_DATABASE', 'chatwoot_dev') %>"
+    username: "<%= ENV.fetch('POSTGRES_USERNAME', 'postgres') %>"
+    password: "<%= ENV.fetch('POSTGRES_PASSWORD', '') %>"
+  primary_replica:
+    <<: *default
+    database: "<%= ENV.fetch('POSTGRES_DATABASE', 'chatwoot_dev') %>"
+    username: "<%= ENV.fetch('POSTGRES_USERNAME', 'postgres') %>"
+    password: "<%= ENV.fetch('POSTGRES_PASSWORD', '') %>"
+    replica: true
 
 test:
-  <<: *default
-  database: "<%= ENV.fetch('POSTGRES_DATABASE', 'chatwoot_test') %>"
-  username: "<%= ENV.fetch('POSTGRES_USERNAME', 'postgres') %>"
-  password: "<%= ENV.fetch('POSTGRES_PASSWORD', '') %>"
+  primary:
+    <<: *default
+    database: "<%= ENV.fetch('POSTGRES_DATABASE', 'chatwoot_test') %>"
+    username: "<%= ENV.fetch('POSTGRES_USERNAME', 'postgres') %>"
+    password: "<%= ENV.fetch('POSTGRES_PASSWORD', '') %>"
+  primary_replica:
+    <<: *default
+    database: "<%= ENV.fetch('POSTGRES_DATABASE', 'chatwoot_test') %>"
+    username: "<%= ENV.fetch('POSTGRES_USERNAME', 'postgres') %>"
+    password: "<%= ENV.fetch('POSTGRES_PASSWORD', '') %>"
+    replica: true
 
 production:
-  <<: *default
-  database: "<%= ENV.fetch('POSTGRES_DATABASE', 'chatwoot_production') %>"
-  username: "<%= ENV.fetch('POSTGRES_USERNAME', 'chatwoot_prod') %>"
-  password: "<%= ENV.fetch('POSTGRES_PASSWORD', 'chatwoot_prod') %>"
+  primary:
+    <<: *default
+    database: "<%= ENV.fetch('POSTGRES_DATABASE', 'chatwoot_production') %>"
+    username: "<%= ENV.fetch('POSTGRES_USERNAME', 'chatwoot_prod') %>"
+    password: "<%= ENV.fetch('POSTGRES_PASSWORD', 'chatwoot_prod') %>"
+  primary_replica:
+    <<: *default
+    host: "<%= ENV.fetch('POSTGRES_HOST_RR', nil) %>"
+    database: "<%= ENV.fetch('POSTGRES_DATABASE', nil) %>"
+    username: "<%= ENV.fetch('POSTGRES_USERNAME_RR', nil) %>"
+    password: "<%= ENV.fetch('POSTGRES_PASSWORD_RR', nil) %>"
+    replica: true

config/environments/development.rb

@@ -70,6 +70,10 @@ Rails.application.configure do
   # require 'syslog/logger'
   config.logger = ActiveSupport::Logger.new(Rails.root.join('log', "#{Rails.env}.log"), 1, ENV.fetch('LOG_SIZE', '1024').to_i.megabytes)
 
+  config.active_record.database_selector = { delay: 2.seconds }
+  config.active_record.database_resolver = ActiveRecord::Middleware::DatabaseSelector::Resolver
+  config.active_record.database_resolver_context = ActiveRecord::Middleware::DatabaseSelector::Resolver::Session
+
   # Bullet configuration to fix the N+1 queries
   config.after_initialize do
     Bullet.enable = true

config/environments/production.rb

@@ -102,5 +102,28 @@ Rails.application.configure do
   # :sendgrid for Sendgrid
   config.action_mailbox.ingress = ENV.fetch('RAILS_INBOUND_EMAIL_SERVICE', 'relay').to_sym
 
+  # Enable Database Selector
+  #
+  # Inserts middleware to perform automatic connection switching.
+  # The `database_selector` hash is used to pass options to the DatabaseSelector
+  # middleware. The `delay` is used to determine how long to wait after a write
+  # to send a subsequent read to the primary.
+  #
+  # The `database_resolver` class is used by the middleware to determine which
+  # database is appropriate to use based on the time delay.
+  #
+  # The `database_resolver_context` class is used by the middleware to set
+  # timestamps for the last write to the primary. The resolver uses the context
+  # class timestamps to determine how long to wait before reading from the
+  # replica.
+  #
+  # By default Rails will store a last write timestamp in the session. The
+  # DatabaseSelector middleware is designed as such you can define your own
+  # strategy for connection switching and pass that into the middleware through
+  # these configuration options.
+  config.active_record.database_selector = { delay: 2.seconds }
+  config.active_record.database_resolver = ActiveRecord::Middleware::DatabaseSelector::Resolver
+  config.active_record.database_resolver_context = ActiveRecord::Middleware::DatabaseSelector::Resolver::Session
+
   Rails.application.routes.default_url_options = { host: ENV['FRONTEND_URL'] }
 end