Tenant Management

What is a tenant?

A tenant is primarily a company, an organization, or a workspace in your product that contains a group of users. A SaaS application provides services to multiple tenants. Tenant is the basic building block of all SaaS applications. Every SaaS feature and experience is built on top of tenants. Nile has built-in tenant virtualization, which makes it easy, reliable, and cost-effective to develop and support SaaS use cases across the globe.

Creating a tenant

Tenant is the basic building block for SaaS and Nile. Nile’s Postgres comes with a built-in tenant table. This table has built-in columns to support common use cases out of the box but can also be extended to add more application-specific columns.

select * from tenants;

id	name	created	updated	deleted

The tenants table has an id that uniquely identifies a tenant, a name of the tenant (customer or workspace etc) and a few other columns for bookkeeping.

Inserting a new customer or tenant could use SQL, one of our SDKs or REST APIs. For product led growth companies, your application would typically dynamically create the new customer when they sign up using our SDK or REST APIs. If you are a more sales led product, you can use Nile’s tenant dashboard to add one.

-- create a record for the first customer
insert into tenants (name) VALUES ('customer1');

You can see how a uuid is automatically inserted into the id column for the customer. This id can be used for all future reference to ‘customer1’.

select * from tenants;

id	name	created	updated	deleted
018ac98e-b37a-731b-b03a-6617e8fd5266	customer1	2023-09-24 23:38:07.097633	2023-09-24 23:38:07.097633

The insert version shown here is the most simple option. You would be able to specify the location (any supported region in the world) or the deployment mode (place tenants on a dedicated instance) of a tenant for more fine grained control. You would also be able to extend the tenants table to add more metadata (covered in later sections).

Adding data for each tenant

Once you have a tenant created, you can insert rows into different tables for a specific tenant. You can create a tenant aware table in Nile by creating a table with a ‘tenant_id’ column of type uuid. tenant_id is a reserved keyword. This is all it takes to ensure you get all the benefits of tenant virtualization in Nile’s Postgres.

For example, if you were building an employee management software that helps with managing employee information, benefits and payroll, it would have an ‘employees’ table to track employees data.

-- creating an employee table that is tenant aware
create table employees (
  tenant_id uuid,
  id integer,
  name text,
  age integer,
  address text,
  start_date timestamp,
  title text,
  CONSTRAINT FK_tenants FOREIGN KEY(tenant_id) REFERENCES tenants(id),
  CONSTRAINT PK_employee PRIMARY KEY(tenant_id,id));

-- adding employees for customer 1
insert into employees (tenant_id, id, name, age, address, start_date, title)
values
  ('018ac98e-b37a-731b-b03a-6617e8fd5266',1345,'Jason',30,'Sunnyvale,California','2016-12-22 19:10:25-07','software engineer'),
  ('018ac98e-b37a-731b-b03a-6617e8fd5266',2423,'Minnie',24,'Seattle,Washingtom','2018-11-11 12:09:22-06','sales engineer'),
  ('018ac98e-b37a-731b-b03a-6617e8fd5266',4532,'Shiva',32,'Fremont, California','2019-09-05 04:03:12-05','product manager');

You can now repeat the same thing for another customer.

-- create the second customer
insert into tenants (name) VALUES ('customer2');

select * from tenants;

id	name	created	updated	deleted
018ac98e-b37a-731b-b03a-6617e8fd5266	customer1	2023-09-24 23:38:07.097633	2023-09-24 23:38:07.097633
018aca35-b8c4-7674-882c-30ae56d7b479	customer2	2023-09-25 02:40:32.964067	2023-09-25 02:40:32.964067

-- insert employees for the second customer
insert into employees (tenant_id, id, name, age, address, start_date, title)
values
  ('018aca35-b8c4-7674-882c-30ae56d7b479',5643,'John',36,'London,UK','2017-12-12 19:10:25-07','senior software engineer'),
  ('018aca35-b8c4-7674-882c-30ae56d7b479',1532,'Mark',27,'Manchester,UK','2022-10-10 12:09:22-06','support engineer'),
  ('018aca35-b8c4-7674-882c-30ae56d7b479',8645,'Sam',42,'Liverpool,UK','2015-08-04 04:03:12-05','product manager');

select * from employees;

tenant_id	id	name	age	address	start_date	title
018ac98e-b37a-731b-b03a-6617e8fd5266	1345	Jason	30	Sunnyvale,California	2016-12-22 19:10:25	software engineer
018ac98e-b37a-731b-b03a-6617e8fd5266	2423	Minnie	24	Seattle,Washington	2018-11-11 12:09:22	sales engineer
018ac98e-b37a-731b-b03a-6617e8fd5266	4532	Shiva	32	Fremont, California	2019-09-05 04:03:12	product manager
018aca35-b8c4-7674-882c-30ae56d7b479	5643	John	36	London,UK	2017-12-12 19:10:25	senior software engineer
018aca35-b8c4-7674-882c-30ae56d7b479	1532	Mark	27	Manchester,UK	2022-10-10 12:09:22	support engineer
018aca35-b8c4-7674-882c-30ae56d7b479	8645	Sam	42	Liverpool,UK	2015-08-04 04:03:12	product manager

Querying tenant data

SaaS applications only query a specific tenant data at any given of time. This requires isolating data to ensure user of one tenant does not read the data of another tenant. You can do this in Nile’s Postgres by simply using the nile.tenant_id session parameter in SQL or directly using our SDK. A good way to think about this is that setting the session context to a tenant is equivalent to pointing to a specific tenant DB. This ensures that all queries are directed to that specific tenant DB. At the same time, you still have access to shared tables (see shared table section).

-- set the session context to a specific tenant
-- who needs to be isolated.
set nile.tenant_id = '018ac98e-b37a-731b-b03a-6617e8fd5266';

select * from employees

tenant_id	id	name	age	address	start_date	title
018ac98e-b37a-731b-b03a-6617e8fd5266	1345	Jason	30	Sunnyvale,California	2016-12-22 19:10:25	software engineer
018ac98e-b37a-731b-b03a-6617e8fd5266	2423	Minnie	24	Seattle,Washington	2018-11-11 12:09:22	sales engineer
018ac98e-b37a-731b-b03a-6617e8fd5266	4532	Shiva	32	Fremont, California	2019-09-05 04:03:12	product manager

Extending tenant information

While Nile’s Postgres has a built in table to store tenants information, it is not strongly opinionated and lets you extend the table to add more columns (read about built in tables). This lets you to store additional metadata for a tenant. You can start storing the address and phone number of each tenant by adding those columns to the tenants table.

ALTER TABLE tenants
  ADD phone_number text,
  ADD address text;

Foreign keys across tenant tables

Extending the employee management SaaS example, we can add information to track the benefits plan that each employee has opted. The ‘benefits’ table has the plan that each employee has opted for and the cost of the plan per year. The employee id is a foreign key reference to the id in the employees table. All the standard behavior of foreign keys are applicable. You can read more about Foreign keys in the official Postgres docs.

create table benefits (
  tenant_id uuid,
  id integer,
  employee_id integer,
  plan_name text,
  expiry_date timestamp,
  cost integer,
  CONSTRAINT FK_tenants FOREIGN KEY(tenant_id) REFERENCES tenants(id),
  CONSTRAINT FK_employees FOREIGN KEY(tenant_id,employee_id) REFERENCES employees(tenant_id,id),
  CONSTRAINT PK_benefits PRIMARY KEY(tenant_id,id));

Note in this example that the foreign key is for the composite tenant_id,employee_id pair. This is because you can have the same employee id across two different tenants or customers.

Transactions for tenants

A common use case would be for SaaS applications to update multiple tables of the same tenant transactionally. For example, you may want to add a new employee record to the ‘employees’ table and their benefits to the ‘benefits’ table transactionally for a specific customer.

BEGIN;
insert into employees (tenant_id, id, name, age, address, start_date, title)
values
  ('018aca35-b8c4-7674-882c-30ae56d7b479',2354,'Adam',22,'Manchester,UK','2016-11-11 12:10:15-08','Designer');

insert into benefits (tenant_id, id, employee_id, plan_name, expiry_date, cost)
values
  ('018aca35-b8c4-7674-882c-30ae56d7b479',1,2354,'Plan A', '2024-11-11 12:10:15-08',1000);
COMMIT;

Deleting tenants

Deleting tenants would typically be used when a customer/tenant deletes their account. You may also want to delete a tenant to save cost when a customer churns due to no activity in the application. The behavior of deleting a tenant would be schema dependent. If the schema has cascading set, all the tenants data will be deleted when a tenant is removed from the tenants table.

delete from tenants where id='018aca35-b8c4-7674-882c-30ae56d7b479';