Hi all,

We are working with a schema-based multi-tenancy database. We are on an Elixir stack, using Postgres, Ecto, and Triplex within a Phoenix Framework project.

We are using the default schema public to store common data, such as users and organisations. In particular, we have within the Organisations table a tenant_prefix column which we use to map a user to their tenancy.

Within a tenancy, we tenant-specific tables. As an example, we have a Products table. When a new organisation is created, we use Triplex to create the schema and run tenant migrations, which create the tenant specific tables such as Products.

As a visual, the database looks like this:

- app_database
  - public
    - users
    - organisations
    - organisations_users

  - tenant1
    - products
    - (other tables...)

  - tenant2
     - products
     - (other tables...)

The Products migration looks like this.

 1 defmodule App.Repo.Migrations.CreateProducts do
 2  use Ecto.Migration
 3
 4  def change do
 5    create table(:products) do
 6      add :title, :string
 7      add :description, :string
 8      add :organisation_id, references(:organisations, on_delete: :nothing), null: false
 9
10      timestamps()
11    end
12
13    create index(:products, [:organisation_id])
14  end
15 end

Right now, it is failing to run because of the Line 8. The erorr that is reported is: ERROR 42P01 (undefined_table) relation "59ef85c702d24d0fac5c7e425d0d3d44.organisations" does not exist

The tenant prefix is a UUID.

So to summarise, we are wondering how to reference the public.organisations table to define the foreign key relationship in tenant.products.