Our challenge

I’m working on a self hosted app (Project, Time Management and Focus Tool) that is a mix of Trello, ClickUp and Toggl and this self-hosted version lets users collaborate in the same workspace .

Some people asked for a live demo preview and, in the future, I also want a hosted version where each workspace has its own dedicated URL, like vinioyama.eigenfocus.com.

How do we make this hosted version work with minimal changes to our existing self-hosted code?

Some constraints:

We don’t want to add workspace_id to every table (projects, users, issues, etc.).
We need to keep the hosted and self-hosted versions as similar as possible and avoid future code conflicts when merging changes from the self-hosted codebase
We need a clean way to separate tenant data while keeping everything manageable

You can check the self-hosted project code here: https://github.com/Eigenfocus/eigenfocus/

Enter: Postgres Schemas

Postgres schemas are like namespaces for organizing database objects.

Instead of creating a workspace_id column in every table, we can use schemas to keep data separate and tables scoped per workspace.

Each tenant/workspace gets its own schema and we can switch schemas based on the current workspace.

Postgres Schemas: How They Work

Postgres decides which tables to use based on the search_path:


SHOW search_path;

Output:


 search_path
--------------
 "$user", public

This means that Postgres looks for tables in the public schema unless told otherwise.

If you’re using Ruby on Rails, you can check by running this in the rails console:


puts ActiveRecord::Base.connection.execute("SHOW search_path;**").to_a

# => {"search_path"=>"\"$user\", public"}

We can create a schema with:

CREATE SCHEMA tenant_1;

and change the search_path dynamically:


SET search_path TO tenant_1, public;

For more details, check out Postgres’ official docs.

From Self-Hosted to Multi-Tenant Hosted: The Plan

For our live demo (and later, the hosted app), here’s what we’ll do:

Keep a workspaces table in the public schema to track tenants.
Create a new schema when a workspace is created, running all necessary migrations.
Switch schemas dynamically based on session data (for the demo) or subdomains (for the hosted version).
Clean up old workspaces after a certain time.

Multitenant with Postgres Schemas in Ruby on Rails

Overview

We want to minimize changes to the Hosted/Demo App codebase. Here are the overall steps:

Use Postgres (the self-hosted uses sqlite) – As we’re using Rails, this is straightforward
Setup ros-apartment gem – helps with managing new schemas, migrations, requests, etc
Implement the Workspace model/table and the concept of a current_workspace in the app
Manage new schemas, sessions and workspaces for each request – The test for this one is interesting
Expire/Erase old workspaces

Ros-Apartment gem setup

This gem https://github.com/rails-on-services/apartment is a fork from the original one (no longer maintained). Here’s the description

Multitenancy for Rails and ActiveRecord
Apartment provides tools to help you deal with multiple tenants in your Rails application. If you need to have certain data sequestered based on account or company, but still allow some data to exist in a common tenant, Apartment can help.

Here’s the main configuration that we’re using for this gem:

config/initializers/apartment.rb


Apartment.configure do |config|
    # ...
    config.tenant_names = -> { Workspace.pluck :id }
    config.use_schemas = true
    # ...
end

With this, the new workspace schema name will be the workspace id. The schemas and tables will look like this:


public.workspaces

WORKSPACE_1.projects
WORKSPACE_1.users
WORKSPACE_1.issues
...

WORKSPACE_2.projects
WORKSPACE_2.users
WORKSPACE_2.issues

The gem also does all the heavy lifting to run new/pending migrations on each existing tenant so you only have to run your well-know bin/rails db:migrate and everything works fine.

Workspace Management

Workspace model

We just have a normal workspace model with a create_with_tenant method to:

Create a workspace
Create/migrate the new corresponding schema


class Workspace < ApplicationRecord
  def self.create_with_tenant
    create.tap do |workspace|
      Apartment::Tenant.create(workspace.id)
    end
  end
end

The Apartment::Tenant.create method creates a new tenant (new schema) and also run the migrations to create the tables.

Request management

In order to manage the requests we’ve modified our ApplicationController to have a current_workspace method and a switch_current_tenant that switches the request to use the current_workspace as the current search path for all queries.

app/controllers/application_controller.rb

class ApplicationController < ActionController::Base

  around_action :switch_current_tenant

  def switch_current_tenant(&action)
    current_workspace.touch
    Apartment::Tenant.switch(current_workspace.id, &action)
  end

  def current_workspace
    @current_workspace ||= if session[:workspace_id].nil?
      Workspace.create_with_tenant.tap do |workspace|
        session[:workspace_id] = workspace.id
      end
    else
      Workspace.find(session[:workspace_id])
    end
  end

Testing the multi tenant feature with session switching

We wanted to assert that two different users should view only the data from their corresponding tenant (stored in the session).

I’ve discovered the using_session method from Capybara that allows you to switch to a different session in the same test.

Here’s a quick example where we create a new project with a user and check that the other user doesn’t see it.

specify "My resources are only accessible for my session" do
    using_session("Me") do
      # ... 
      visit projects_path

      click_link "Create project"
      fill_in :project_name, with: "My project"
      click_button "Create"

      expect(page).to have_content("My project")
    end

    using_session("Another User") do

      # ... 
      visit projects_path

      expect(page).not_to have_content("My project")
    end

    using_session("Me") do
      visit projects_path

      expect(page).to have_content("My project")
    end
end

By using using_session we can also test that the workspaces are being correctly created. Example:


  specify "When my workspace does not exist anymore, a new one is created" do
    using_session("Me") do
      visit root_path

      expect(page).to have_content("Before starting, we need you to fill your preferred language and time zone.")
    end

    expect(Workspace.count).to eq(1)

    Workspace.destroy_all

    using_session("Me") do
      visit root_path

      expect(page).to have_content("Before starting, we need you to fill your preferred language and time zone.")
    end

    expect(Workspace.count).to eq(1)
  end

Conclusion

I’ve also used schemas before and they are an excellent alternative to manage multiple tenants and facilitates dealing with data privacy as we do not have to remember to scope by a tenant_id.

Also, as the ‘Demo’ / ‘Hosted’ version should basically be the self-hosted one with minimal changes for features like workspace setup, payments or invoices this solution fits perfectly with my current needs where I want to minimize conflicts.

Check the code

The app has a lot to evolve and I’m also planning on creating posts or videos showing how to code some features using turbo/turbo streams and action cable.

You can clone for follow the repository here https://github.com/Eigenfocus/eigenfocus/