Tenant Sharing

While Nile provides a native approach to isolating data between tenants, it also supports sharing data across tenants using shared tables.

For example

1. A corporate travel booking site like TripActions (https://www.navan.com/) or Concur helps company employees book flights and manage their trips. Every company that uses the product must see a list of available flights. The list of flights can be stored in a shared table accessible to all tenants in any location.
2. A CRM product like Salesforce or Hubspot lists companies by categories that Sales reps can choose from for outreach. The list of companies and their categories is a global list that can be made available to all customers using shared tables.
3. Your application is an Infrastructure SaaS product (e.g., Snowflake or Databricks) and wants to provide a list of cloud providers and regions for users to select where they want to deploy their infrastructure. The data about all the cloud providers and the available regions can be in a shared table, and all the tenants can access it.

Creating and inserting data into shared tables

To create a shared table, you must create a standard table without a tenant_id column. Nile ensures that the data in the shared table is available across all tenants.

create table flights (
  id integer PRIMARY KEY,
  name text,
  from_location text,
  to_location text,
  departure_time TIMESTAMP,
  arrival_time TIMESTAMP);

A table with a list of flights for a corporate travel booking site. The list of flights can be shared across tenants.

Inserting into shared table works as normal with SQL (TBD JS)

insert into flights
  (id, name, from_location, to_location, departure_time, arrival_time)
values
  (643, 'United','SFO','NY','2023-12-22 19:10:25-07','2016-12-23 05:10:25-07'),
  (123, 'Indian Airways','Mumbai','Delhi','2024-11-23 19:10:25-07','2024-11-23 01:10:25-07'),
  (384, 'Singapore Airlines','SGR','LDN','2024-10-23 04:10:25-07','2024-11-23 10:10:25-07'),
  (724, 'British Airways','LDN','Paris','2024-07-04 11:10:25-07','2024-07-04 17:10:25-07');

Most use cases for shared tables will involve loading data from some external source instead of manual inserts into the table. For example, to populate the flights table, you will typically load the data from an airline data company (eg. Sabre) and also keep it upto date by subscribing to real time changes.

Querying data from shared tables

Since shared tables are just standard Postgres tables, all types of queries work on Shared tables.

Selecting the flights that start from SFO

select * from flights where from_location='SFO';

You can also query by joining multiple shared tables. We can join the “flights” table with “airports” table to get more detailed information about the destination from where the flights are leaving.

-- the airports table where each entry represent a unique airport in the world
create table airports (
  id integer PRIMARY KEY,
  name text,
  code text,
  address text,
  number_of_terminals integer);

-- entry of a few airports in the world with detailed information for each
insert into airports (id, name, code, address, number_of_terminals)
values
  (3459,'San Francisco','SFO','San Francisco, CA 94128','10'),
  (2933,'Singapore','SGR','PO Box 168 Changi Airport Singapore 918146','15'),
  (2234,'London','LDN','Hounslow TW6 1QG, UK','12');

-- displaying all the flight information along with the address of the airport
-- from which the flight is starting
select flights.id, flights.name, flights.from_location, airports.address
from flights, airports
where flights.from_location=airports.code;

Joining data between tenant aware and shared tables

Tenant aware tables can be joined with shared tables to provide relevant information for a specific tenant. For example, let us consider a bookings table that has all the flights booked by employees of a specific company(or tenant) with id 7543a610-f068-45fa-8ba0-7bc884bd29c2. Each booking will refer to the flights id in the ‘flights’ table. It would be trivial to show detailed flight information along with every booking by joining the two tables.

-- the bookings table where each row represents a single booking
-- for a specific employee within a customer/tenant
create table bookings (
  tenant_id uuid,
  booking_id integer,
  employee_id text,
  flight_id integer,
  total_price float,
  PRIMARY KEY(tenant_id,booking_id));

-- inserting rows for all the bookings done by employees in a
-- specific tenant
insert into bookings (tenant_id, booking_id, employee_id, flight_id, total_price)
values
  ('7543a610-f068-45fa-8ba0-7bc884bd29c2',1345,2,643, 200.00),
  ('7543a610-f068-45fa-8ba0-7bc884bd29c2',2456,3,123, 150.00),
  ('7543a610-f068-45fa-8ba0-7bc884bd29c2',3240,4,123, 250.00);

-- set tenant context to ensure selects are isolated only to this tenant
set nile.tenant_id = '7543a610-f068-45fa-8ba0-7bc884bd29c2';

-- join query between bookings and flights to display each booking info
-- in the application with detailed information about the flight
select bookings.booking_id, bookings.employee_id, flights.name,
       flights.from_location, flights.to_location, flights.departure_time,
       flights.arrival_time, bookings.total_price
from bookings,flights
where bookings.flight_id=flights.id;

Foreign keys for shared tables

A shared table can have a foreign key reference to another shared table. In the travel example, the flights table can have a reference to the airport id. So, the ‘flights’ table can be modified to have an airport id column that is a foreign reference to the id column in the ‘airports’ table

-- flights table's airport_id is a reference to the id column
-- in table airports
create table flights (
  id integer PRIMARY KEY,
  airport_id integer,
  name text,
  from_location text,
  to_location text,
  departure_time TIMESTAMP,
  arrival_time TIMESTAMP,
  **CONSTRAINT FK_airport_id FOREIGN KEY(airport_id) REFERENCES airports(id)**
  );

Transactions with shared tables

Finally, it is possible to do writes to multiple shared tables in the same transaction. A good use case for this is to ensure multiple shared tables are in a consistent state when they are loaded with data. While the example above showed that the flights and airport tables were populated separately, another alternative is to ensure that for each airport that is inserted in the ‘airports’ table, all the flights are updated in the ‘flights’ table. This is specifically useful when data is bootstrapped from some external source.

BEGIN;
insert into airports (id, name, code, address, number_of_terminals)
values
  (4956,'Seattle','SEA','17801 International Blvd','15');

insert into flights
  (id, name, from_location, to_location, departure_time, arrival_time)
values
  (942, 'Alaskan','SEA','SFO','2024-12-22 11:10:25-07','2024-12-22 04:10:25-07'),
  (342, 'United','SEA','PHX','2024-10-22 16:10:25-07','2024-10-22 18:10:25-07');
COMMIT;