Multi-tenancy

Uniqueness that stops at the tenant boundary

Difficulty 2/5Free

A B2B product sells to companies. Every company is a tenant, and each tenant has its own members.

The rules the business actually has:

A person's email address identifies them inside a tenant, not across the whole product. The same consultant can be a member of Acme and of Globex.
Inside one tenant, an email appears at most once. Inviting someone who is already a member is a mistake, not a second membership.
Every membership belongs to a tenant that exists. A membership pointing at a deleted or imaginary company is not data, it is a bug.
A member's role is one of owner, admin or member. Nothing else.

The tenants table already exists. Design the memberships.

The one thing that is fixed

2 relations

The tests reference these names. Everything else is yours, and is what is being assessed: extra tables, extra columns, types, constraints, indexes.

tenants
idslug
memberships
idtenant_idemailrole

The schema that already exists

This runs before your submission. Do not repeat it, extend it.

prelude.sql

CREATE TABLE tenants (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL
);

INSERT INTO tenants (slug, name) VALUES ('acme', 'Acme Inc'), ('globex', 'Globex');

Your schemaPostgreSQL DDL: tables, constraints, indexes

schema.sql

CREATE TABLE memberships (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  email     TEXT NOT NULL,
  role      TEXT NOT NULL
);

-- tenants already exists. Wire memberships to it, and make the four rules hold.

Tab indents · ⌘/Ctrl + Enter runs

What will be checked

6 tests

····Someone can be invited to a tenant
····The same person can also be a member of another tenant
····The same person cannot be invited twice to one tenant
····A membership cannot point at a tenant that does not exist
····A role outside the three allowed values is refused
····Acme ended up with exactly one member