Makerkit #4 - Databases: Supabase, RLS and Modelling

Welcome to the fourth episode of our series. 👋

So far we have been playing with ready-made bricks: configuring colours, starting containers. Today we go into the engine room and design the database.

In the Makerkit world — and in modern Next.js generally — a database is not merely "a container for text". Supabase, built on PostgreSQL, takes on an enormous share of the logic:

1. Relationships between data (the schema).
2. Security (Row Level Security — RLS).
3. Business logic (functions and triggers).

Understanding this is essential if your SaaS is to be scalable and safe. Buckle up: we are going into SQL. 💾

🔄 The developer's workflow

Many beginners make the mistake of trying to make changes by clicking around the Supabase dashboard in the browser. Do not do that. In a professional project we work locally, using migrations.

The Makerkit workflow looks like this:

1. Edit: create an SQL migration file.
2. Reset: apply the changes to the local database (pnpm run supabase:web:reset).
3. Typegen: generate TypeScript types from the database (pnpm run supabase:web:typegen).
4. Code: use the new, fully typed tables in Next.js.

That keeps your database always in sync with the code, and TypeScript watches whether you are trying to select a column that does not exist.

🏗️ Schema design

The heart of Makerkit is the public.accounts table. It holds everything together. Every element of your system — projects, invoices, tickets — has to belong to an account (which may be an individual user OR a team).

Let us build an example support ticket system; it is a perfect illustration of relationships.

1. Creating the migration

In the terminal:

pnpm --filter web supabase migration new support-schema

That creates an empty SQL file in apps/web/supabase/migrations.

2. Defining the table

Here is what a professional table definition looks like in SQL. Note the use of ENUM for statuses — it prevents typos in the code.

-- Define the statuses (better than a plain string!)
create type public.ticket_status as enum ('open', 'closed', 'resolved', 'in_progress');

create table if not exists public.tickets (
  id uuid primary key default gen_random_uuid(),
  -- CRUCIAL: the link to the account (the data's owner)
  account_id uuid not null references public.accounts(id) on delete cascade,
  title varchar(255) not null,
  status public.ticket_status not null default 'open',
  -- Who created / was assigned the ticket?
  assigned_to uuid references public.accounts(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- An index speeds up lookups by account (very important!)
create index ix_tickets_account_id on public.tickets(account_id);

🛡️ RLS: your personal bouncer

Now the most important part. By default in Supabase anyone can do anything, given an API key. We have to switch on Row Level Security (RLS).

RLS is a mechanism where the database checks every row before handing it over. It is the bouncer at a club, checking each guest's ID individually.

Step 1: zero trust (revoke everything)

First we lock it all down.

revoke all on public.tickets from public, service_role;

Step 2: open the door to signed-in users

grant select, insert, update, delete on public.tickets to authenticated;
grant select, insert on public.tickets to service_role;

Step 3: enable RLS and define the policy

The policy below says: "a user may see this ticket ONLY IF they belong to the account that owns it".

alter table public.tickets enable row level security;

create policy select_tickets
  on public.tickets
  for select
  to authenticated
  using (
    -- A function that checks membership
    public.has_role_on_account(account_id)
  );

👮‍♂️ Hard mode: granular permissions (RBAC)

Makerkit goes a step further. What if you want everyone in the company to see tickets, but only the owner to be able to delete them? A plain has_role_on_account is not enough here.

We need Makerkit's permission system (public.role_permissions).

1. Add the permission to the system:

   alter type public.app_permissions add value 'tickets.delete';
   

2. Assign the permission to the owner role:

   insert into public.role_permissions(role, permission)
   values ('owner', 'tickets.delete');
   

3. Create a restrictive RLS policy:

   create policy delete_tickets
     on public.tickets
     for delete
     to authenticated
     using (
       -- We check not only the account but the specific permission!
       public.has_permission(auth.uid(), account_id, 'tickets.delete'::app_permissions)
     );
   

This is enterprise-grade. It makes your application ready for large companies with complicated staff structures.

🧠 Logic in the database: functions and triggers

PostgreSQL allows automation. Rather than writing in JS "when the status changes to closed, set the closing date", let us do it in the database. That guarantees data consistency.

But we can do far more interesting things — for instance enforcing plan limits (billing). Imagine a check_ticket_limit function that runs BEFORE a new ticket is inserted:

create trigger check_ticket_limit
before insert on public.tickets
for each row
execute function public.check_ticket_limit();

Such a function — described in the Makerkit documentation — checks whether the company has an active subscription and whether it has exceeded a limit of, say, 50 tickets a month. If it has, the database rejects the write and returns an error. Absolute security.

🌱 Seeding: data from the start

Working on an empty database is hard and difficult to reason about — it brings to mind learning programming from books or lectures, where the level of abstraction defeats even the sharpest minds. Makerkit uses a seed.sql file in the supabase/ folder to load starting data on every database reset.

It is worth adding some example tickets there:

INSERT INTO public.tickets (account_id, title, status, priority)
VALUES
  ('your-account-id', 'Problem signing in', 'open', 'high'),
  ('your-account-id', 'Error on the invoice', 'in_progress', 'medium');

That way, after running pnpm run supabase:web:reset you immediately have an application full of data for testing the UI.

⌨️ Typegen: the TypeScript magic

Once the migrations are applied, time for the reward. We run:

pnpm run supabase:web:typegen

This script scans your local Postgres database and generates database.types.ts. Now your Next.js code has full type hints. TypeScript knows that status can only be 'open' | 'closed' and not an arbitrary string.

💡 Summary

Working with a database in Makerkit is more than CREATE TABLE. It is building a secure fortress, where:

1. The schema defines structure and relationships.
2. RLS and RBAC make sure nobody sees — or deletes — data without permission.
3. Triggers handle automation and enforce business limits (billing).
4. Typegen ties it all to your Next.js frontend.

With foundations this solid we can move on to building the user interface, knowing that our data is safe. 🛡️

In the next episode we tackle Server Components and fetching that data on the frontend.

Source: Makerkit Course - Database Schema and Migrations