To summarize, the Postgres Database model contains the following tables: users, organizations, memberships, subscriptions and organizations_subscriptions.

Users

Users are, of course, foundational to any application.

If a user signed in using Supabase Authentication, it merely means we have verified their credentials, not that they have an account.

We have to create an additional collection to store a user's data, such as:

names (first name, last name)
profile photo
settings
any other information which is not possible to update using their Authentication record (email, and sign-in providers)

Below is the users table:


create table users (
  id uuid references auth.users not null primary key,
  photo_url text,
  display_name text,
  onboarded bool not null
);

Organizations

Organizations are groups of users.

If the name doesn't suit your domain, don't worry: you can always change the terminology using the localization files.

Below is what the Organizations schema looks like:


create table organizations (
  id bigint generated always as identity primary key,
  uuid uuid not null default uuid_generate_v4(),
  name text not null,
  logo_url text
);

Memberships

To link an user to an organization we use another table named memberships.

A user is associated to this table when:

it is part of the organization
it is not yet part, but has been invited

Furthermore, in this table we store the role property which defines the role of the user for the organization.

Below is what the Organizations schema looks like:


create table memberships (
  id bigint generated always as identity primary key,
  user_id uuid references public.users,
  organization_id bigint not null references public.organizations,
  role int not null,
  invited_email text,
  code text,
  unique (user_id, organization_id)
);

Invites

To create an invite to join an organization, we create a membership for the user with two properties code and invite_email: these two properties are going to become null once the invite gets accepted.

User Roles

By default, MakerKit defines three roles: Owner (can be only one), Admin, and Member.

This enum is hierarchical and associated with a number:


enum MembershipRole {
  Member = 0,
  Admin = 1,
  Owner = 2
}

An Admin can do anything a member can do, and an Owner can do anything an Admin can.

Subscriptions

When users complete the Stripe checkout, the application will store some information from the subscription object sent by Stripe.

Below is the schema of the information stored in the Database:


create table subscriptions (
  id text not null primary key,
  price_id text not null,
  status subscription_status not null,
  currency text,
  interval text,
  interval_count int,
  created_at timestamptz,
  period_starts_at timestamptz,
  period_ends_at timestamptz,
  trial_starts_at timestamptz,
  trial_ends_at timestamptz
);

To link a customer_id from Stripe with an organization, we use a join table organizations_subscriptions:


create table organizations_subscriptions (
  organization_id bigint not null references public.organizations (id) on delete cascade,
  subscription_id text unique references public.subscriptions (id) on delete set null,
  customer_id text not null unique,
  primary key (organization_id)
);

We store the customer_id property so that, when an organization is first linked to a Stripe customer, can access their Stripe data using the Billing Portal.

When a subscription is active, the subscription_id field will be populated with the subscription ID. Otherwise, the field will be null.

Storage

By default, the kit also creates two Storage buckets: logos and avatars:


insert into storage.buckets (id, name, PUBLIC)
  values ('logos', 'logos', true);
 
insert into storage.buckets (id, name, PUBLIC)
  values ('avatars', 'avatars', true);