Adding a Projects Data Model to Your Next.js Supabase Application

Learn how to add a Projects data model to your Next.js Supabase application using Supabase

Team Accounts allow you to manage several members in a single account. These usually share a subscription and other common attributes. However, you may need to go deeper than that, and allow team accounts to have a set of projects that they can collaborate on.

This guide will walk you through implementing a robust project system in your Makerkit application using Supabase. We'll cover everything from setting up the necessary database tables to implementing role-based access control.

Overview of the Projects Data Model

The Projects feature allows users to create and manage projects within your application. It includes:

  • Project creation and management
  • Role-based access control (owner, admin, member)
  • Permissions system
  • Database schema and functions

Let's dive in!

Database Schema and Functions

We'll start by setting up the database schema. This involves creating tables, enums, and functions.

Enums

First, let's create two enum types:

sql
-- Project roles
CREATE TYPE public.project_role AS ENUM ('owner', 'admin', 'member');
-- Project actions
CREATE TYPE public.project_action AS ENUM (
  'view_project',
  'edit_project',
  'delete_project',
  'invite_member',
  'remove_member'
);

These enums define the possible roles and actions within a project.

Tables

Now, let's create the main tables.

We will create two tables: projects and project_members.

The projects table stores project information, while the project_members table manages the relationship between projects and users.

Projects Table

sql
-- Projects table
CREATE TABLE IF NOT EXISTS public.projects (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  account_id UUID NOT NULL REFERENCES public.accounts(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Let's dive into the projects table:

  • id is a unique identifier for each project.
  • name is the name of the project.
  • description is an optional description for the project.
  • account_id is a foreign key referencing the accounts table.
  • created_at and updated_at are timestamps for when the project was created and updated, respectively.

Project Members Table

The project_members table stores the relationship between projects and users:

sql
-- Project members table
CREATE TABLE IF NOT EXISTS public.project_members (
  project_id UUID NOT NULL REFERENCES public.projects(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role public.project_role NOT NULL DEFAULT 'member',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (project_id, user_id)
);

Let's clarify the project_members table:

  • The project_id column is a foreign key referencing the projects table.
  • The user_id column is a foreign key referencing the auth.users table.
  • The role column is an enum type that can have three values: owner, admin, or member.

These columns ensure that each project has a unique owner, and that each member can have a role of owner, admin, or member.

Indexes

To optimize query performance, let's add some indexes:

sql
CREATE INDEX projects_account_id ON public.projects (account_id);
CREATE INDEX project_members_project_id ON public.project_members (project_id);
CREATE INDEX project_members_user_id ON public.project_members (user_id);

We also need a unique index to ensure only one owner per project:

sql
CREATE UNIQUE INDEX projects_unique_owner ON public.project_members (project_id) WHERE role = 'owner';

Permissions

Set up default permissions. This is useful to make sure we can granularly control access to the tables.

sql
alter table public.projects enable row level security;
alter table public.project_members enable row level security;
-- Revoke all permissions by default
REVOKE ALL ON public.projects FROM public, service_role;
REVOKE ALL ON public.project_members FROM public, service_role;
-- Grant access to authenticated users
GRANT SELECT, INSERT, UPDATE, DELETE ON public.projects TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.project_members TO authenticated;

Functions

Now, let's create several helper functions to manage projects and permissions.

Is Project Member

This function checks if a user is a member of a specific project:

sql
-- public.is_project_member
-- this function checks if a user is a member of a specific project
create or replace function public.is_project_member(p_project_id uuid)
returns boolean
set search_path = ''
as $$
  select exists (
    select 1 from public.project_members
    where project_id = p_project_id
    and account_id = (select auth.uid())
  );
$$ language sql security definer;
grant execute on function public.is_project_member(uuid) to authenticated;

Is Project Admin

This function checks if a user is an admin or owner of a specific project:

sql
-- public.is_project_admin
-- this function checks if a user is an admin or owner of a specific project
create or replace function public.is_project_admin(p_project_id uuid)
returns boolean
set search_path = ''
as $$
  select exists (
    select 1 from public.project_members
    where project_id = p_project_id
    and account_id = (select auth.uid())
    and role in ('owner', 'admin')
  );
$$ language sql;
grant execute on function public.is_project_admin to authenticated;

Is Project Owner

This function checks if a user is the owner of a project:

sql
-- public.is_project_owner
-- check if a user is the owner of a project
create or replace function public.is_project_owner(project_id uuid)
returns boolean
set search_path = ''
as $$
  select exists (
    select 1 from public.project_members
    where project_id = $1
    and account_id = (select auth.uid())
    and role = 'owner'
  );
$$ language sql;
grant execute on function public.is_project_owner to authenticated;

User Has Project Permission

This function checks if a user has the required permissions to perform a specific action on a project:

sql
-- public.user_has_project_permission
-- check if the current user has the required permissions to perform a specific action on a project
create or replace function public.user_has_project_permission(
  p_user_auth_id uuid,
  p_project_id uuid,
  p_action public.project_action
)
returns boolean
set search_path = ''
as $$
declare
  v_role public.project_role;
begin
  -- first, check if the user is a member of the project
  select role into v_role
  from public.project_members
  where project_id = p_project_id and account_id = p_user_auth_id;
  if v_role is null then
    return false;
  end if;
  -- check permissions based on role and action
  case v_role
    when 'owner' then
      return true;  -- owners can do everything
    when 'admin' then
      return p_action != 'delete_project';  -- admins can do everything except delete the project
    when 'member' then
      return p_action in ('view_project');
    else
      raise exception 'user must be a member of the project to perform this action';
  end case;
end;
$$ language plpgsql;
grant execute on function public.user_has_project_permission to authenticated;

In the above, we use the CASE statement to check the role of the user and the action they are trying to perform. If the user is a member of the project and has the required permissions, we return TRUE. Otherwise, we raise an exception.

Please feel free to modify the user_has_project_permission function to fit your specific use case and your project's requirements.For example, you may want to add more roles or actions, or restrict the permissions based on the project's settings.

Current User Has Project Permission

This function is a wrapper around user_has_project_permission for the current user:

sql
-- public.current_user_has_project_permission
create or replace function public.current_user_has_project_permission(
  p_project_id uuid,
  p_action public.project_action
)
returns boolean
set search_path = ''
as $$
begin
  return public.user_has_project_permission((select auth.uid()), p_project_id, p_action);
end;
$$ language plpgsql;
grant execute on function public.current_user_has_project_permission to authenticated;

Current User Can Manage Project Member

This function checks if the current user can manage another user in a project:

sql
-- public.current_user_can_manage_project_member
-- Function to check if a user can manage another user in a project
create or replace function public.current_user_can_manage_project_member(
  p_target_member_role public.project_role,
  p_project_id uuid
)
returns boolean
set search_path = ''
as $$
declare
  v_current_user_role public.project_role;
begin
  select role into v_current_user_role
  from public.project_members
  where project_id = p_project_id and account_id = (select auth.uid());
  if v_current_user_role is null or p_target_member_role is null then
    raise exception 'User not found';
  end if;
  -- Check if the manager has a higher role
  return (v_current_user_role = 'owner' and p_target_member_role != 'owner') or
         (v_current_user_role = 'admin' and p_target_member_role = 'member');
end;
$$ language plpgsql;
grant execute on function public.current_user_can_manage_project_member to authenticated;

This project is extremely useful to verify that a user can perform actions that affect another user in a project.

Update Project Member Role

This function updates the role of a project member:

sql
-- public.update_project_member_role
-- function to update the role of a project member
create or replace function public.update_project_member_role(
  p_user_id uuid,
  p_new_role public.project_role,
  p_project_id uuid
)
returns boolean
set search_path = ''
as $$
declare
  v_current_role public.project_role;
begin
  -- Get the current role of the member
  select role into v_current_role
  from public.project_members
  where project_id = p_project_id and account_id = p_user_id;
  -- Check if the manager can manage this member
  if not public.current_user_can_manage_project_member(v_current_role, p_project_id) then
    raise exception 'Permission denied';
  end if;
  if p_new_role = 'owner' then
    raise exception 'Owner cannot be updated to a different role';
  end if;
  -- Update the member's role
  update public.project_members
  set role = p_new_role
  where project_id = p_project_id and account_id = p_user_id;
  return true;
end;
$$ language plpgsql;
grant execute on function public.update_project_member_role to authenticated;

Can Edit Project

This function checks if a user can edit a project:

sql
-- public.can_edit_project
-- check if the user can edit the project
create or replace function public.can_edit_project(p_user_auth_id uuid, p_project_id uuid)
returns boolean
set search_path = ''
as $$
  select public.user_has_project_permission(p_user_auth_id, p_project_id, 'edit_project'::public.project_action);
$$ language sql;
grant execute on function public.can_edit_project to authenticated;

This is a simple wrapper around the user_has_project_permission function that checks if the current user can edit a project.

Can Delete Project

This function checks if a user can delete a project:

sql
CREATE OR REPLACE FUNCTION public.can_delete_project(p_user_auth_id UUID, p_project_id UUID)
RETURNS BOOLEAN
SET search_path = ''
AS $$
  SELECT public.user_has_project_permission(p_user_auth_id, p_project_id, 'delete_project'::public.project_action);
$$ LANGUAGE sql;
GRANT EXECUTE ON FUNCTION public.can_delete_project TO authenticated;

This is a simple wrapper around the user_has_project_permission function that checks if the current user can delete a project.

Can Invite Project Member

This function checks if a user can invite a new member to the project:

sql
-- public.can_invite_project_member
-- check if the user can invite a new member to the project
create or replace function public.can_invite_project_member(p_user_auth_id uuid, p_project_id uuid)
returns boolean
set search_path = ''
as $$
  select public.user_has_project_permission(p_user_auth_id, p_project_id, 'invite_member'::public.project_action);
$$ language sql;
grant execute on function public.can_invite_project_member to authenticated;

This is also a simple wrapper around the user_has_project_permission function that checks if the current user can invite a new member to the project.

Row Level Security (RLS) Policies

Now, let's set up RLS policies to secure our tables. RLS policies are used to control access to specific columns and rows in a table. The functions we defined earlier are used to check if a user has the required permissions to perform a specific action on a project.

Projects Table Policies

sql
/*
RLS POLICIES
*/
-- SELECT(public.projects)
create policy select_projects
  on public.projects
  for select
  to authenticated
  using (
    public.is_project_member(id)
  );
-- INSERT(public.projects)
create policy insert_new_project
  on public.projects
  for insert
  to authenticated
  with check (
    public.has_role_on_account(account_id)
  );
-- DELETE(public.projects)
create policy delete_project
  on public.projects
  for delete
  to authenticated
  using (
    public.can_delete_project((select auth.uid()), id)
  );
-- UPDATE(public.projects)
create policy update_project
  on public.projects
  for update
  to authenticated
  using (
    public.can_edit_project((select auth.uid()), id)
  )
  with check (
    public.can_edit_project((select auth.uid()), id)
  );

Alright, let's break down the policies:

  1. Select Projects: This policy allows authenticated users to select projects from the projects table. If a user is not a member of the project, they will not be able to see the project.
  2. Insert New Project: This policy allows authenticated users to insert new projects into the projects table. It does so by checking if the current user is currently a member of the team account associated with the project.
  3. Delete Project: This policy allows authenticated users to delete projects from the projects table. It verifies that the user can delete the project using the can_delete_project function.
  4. Update Project: This policy allows authenticated users to update projects in the projects table. It does so by checking if the current user has the required permissions to edit the project using the can_edit_project function.

Project Members Table Policies

sql
-- SELECT(public.project_members)
create policy select_project_members
  on public.project_members
  for select
  to authenticated
  using (
    public.is_project_member(project_id)
  );
-- INSERT(public.project_members)
create policy insert_project_member
  on public.project_members
  for insert
  to authenticated
  with check (
    public.can_invite_project_member(
      (select auth.uid()),
      project_id
    )
  );
-- UPDATE(public.project_members)
create policy update_project_members
  on public.project_members
  for update
  to authenticated
  using (
    public.current_user_can_manage_project_member(
      role,
      project_id
    )
  )
  with check (
    public.current_user_can_manage_project_member(
      role,
      project_id
    )
  );
-- DELETE(public.project_members)
create policy delete_project_members
  on public.project_members
  for delete
  to authenticated
  using (
    public.current_user_can_manage_project_member(
      role,
      project_id
    )
  );

Let's break down the policies:

  1. Select Project Members: This policy allows authenticated users to select project members from the project_members table. If a user is not a member of the project, they will not be able to see the project members.
  2. Insert Project Member: This policy allows authenticated users to insert new project members into the project_members table. It does so by checking if the current user can invite a new member to the project using the can_invite_project_member function.
  3. Update Project Members: This policy allows authenticated users to update project members in the project_members table. It does so by checking if the current user can manage the project member using the current_user_can_manage_project_member function.
  4. Delete Project Members: This policy allows authenticated users to delete project members from the project_members table. It does so by checking if the current user can manage the project member using the current_user_can_manage_project_member function.

Additional Functions

Let's add a few more helpful functions:

Add Project Owner

This function adds the owner of the project as the first project member:

sql
CREATE OR REPLACE FUNCTION kit.add_project_owner()
RETURNS TRIGGER
AS $$
BEGIN
  INSERT INTO public.project_members (project_id, user_id, role)
  VALUES (NEW.id, auth.uid(), 'owner'::public.project_role);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Trigger to add owner of the project creator as the first project member
CREATE TRIGGER add_project_owner_on_insert
    AFTER INSERT ON public.projects
    FOR EACH ROW
    EXECUTE PROCEDURE kit.add_project_owner();

Add Project Member

This function adds a new member to a project:

sql
CREATE OR REPLACE FUNCTION public.add_project_member(
  p_project_id UUID,
  p_user_id UUID,
  p_role public.project_role DEFAULT 'member'
) RETURNS BOOLEAN
SET search_path = ''
AS $$
DECLARE
  v_account_id UUID;
  v_is_personal_account boolean;
BEGIN
  -- Get the account_id for the project
  SELECT account_id INTO v_account_id
  FROM public.projects
  WHERE id = p_project_id;
  -- check if the target user account is a personal account (not a team account)Add commentMore actions
  -- Use security definer context to bypass RLS for this validation
  select is_personal_account into v_is_personal_account
  from public.accounts
  where id = p_user_id;
  if v_is_personal_account is null then
    raise exception 'user account not found';
  end if;
  if not v_is_personal_account then
    raise exception 'cannot invite team accounts to projects - only individual users can be invited';
  end if;
  -- check if the current user has permission to add members
  if not public.is_project_admin(p_project_id) or p_role = 'owner' then
    raise exception 'permission denied';
  end if;
  -- Check if the user is a member of the team account
  IF NOT EXISTS (
    SELECT 1 FROM public.accounts_memberships
    WHERE account_id = v_account_id AND user_id = p_user_id
  ) THEN
    RAISE EXCEPTION 'User is not a member of the team account';
  END IF;
  -- Add the new member (the trigger will enforce the team membership check)
  INSERT INTO public.project_members (project_id, user_id, role)
  VALUES (p_project_id, p_user_id, p_role)
  ON CONFLICT (project_id, user_id) DO UPDATE
  SET role = EXCLUDED.role;
  RETURN TRUE;
END;
$$ LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION public.add_project_member TO authenticated;

Check Project Member in Team

This trigger function ensures that a user being added to a project is already a member of the associated team account:

sql
CREATE OR REPLACE FUNCTION kit.check_project_member_in_team()
RETURNS TRIGGER
AS $$
DECLARE
  v_account_id UUID;
  v_is_personal_account boolean;
BEGIN
  SELECT account_id FROM public.projects
  WHERE id = NEW.project_id
  INTO v_account_id;
  -- check if the account being added is a personal account (not a team account)Add commentMore actions
  select is_personal_account into v_is_personal_account
  from public.accounts
  where id = new.account_id;
  if v_is_personal_account is null then
    raise exception 'account not found';
  end if;
  if not v_is_personal_account then
    raise exception 'cannot add team accounts to projects - only individual user accounts can be project members';
  end if;
  IF NOT EXISTS (
    SELECT 1 FROM public.accounts_memberships
    WHERE account_id = v_account_id AND user_id = NEW.user_id
  ) THEN
    RAISE EXCEPTION 'User must be a member of the team account to be added to the project';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Create a trigger that uses the above function
CREATE TRIGGER ensure_project_member_in_team
BEFORE INSERT OR UPDATE ON public.project_members
FOR EACH ROW EXECUTE FUNCTION kit.check_project_member_in_team();

Implementing the Projects Feature

Now that we've set up all the necessary database objects, let's walk through how to use this Projects feature in your Makerkit application.

Creating a New Project

To create a new project, you'll insert a row into the public.projects table. The kit.add_project_owner() trigger will automatically add the creating user as the owner.

sql
INSERT INTO public.projects (name, description, account_id)
VALUES ('My New Project', 'This is a description of my project', :account_id);

Replace :account_id with the actual account ID.

Adding Members to a Project

Use the public.add_project_member function to add new members to a project:

sql
SELECT public.add_project_member(:project_id, :user_id, 'member'::public.project_role);

Replace :project_id and :user_id with actual values. The role can be 'member' or 'admin'.

Updating a Member's Role

To change a member's role, use the public.update_project_member_role function:

sql
SELECT public.update_project_member_role(:user_id, 'admin'::public.project_role, :project_id);

Querying Projects

To get all projects a user is a member of:

sql
SELECT * FROM public.projects
WHERE id IN (
  SELECT project_id
  FROM public.project_members
  WHERE user_id = auth.uid()
);

Checking Permissions

You can use the various permission-checking functions in your application logic. For example:

sql
-- Check if the current user can edit a project
SELECT public.can_edit_project(auth.uid(), :project_id);
-- Check if the current user can delete a project
SELECT public.can_delete_project(auth.uid(), :project_id);
-- Check if the current user can invite members to a project
SELECT public.can_invite_project_member(auth.uid(), :project_id);

Application-Level Logic

Now that we've covered the database schema and functions, let's move on to the application-level logic.

The ProjectsService

To provide a better API for interacting with the database, we can create a service layer (ProjectsService) that encapsulates all the database operations related to projects. This is a great practice as it separates concerns and makes your code more maintainable.

The ProjectsService class encapsulates all the database operations related to projects. This is a great practice as it separates concerns and makes your code more maintainable.

apps/web/lib/server/projects/projects.service.ts
import { SupabaseClient } from '@supabase/supabase-js';
import { Database, Tables } from '~/lib/database.types';
type ProjectAction = Database['public']['Enums']['project_action'];
export function createProjectsService(client: SupabaseClient<Database>) {
 return new ProjectsService(client);
}
class ProjectsService {
 constructor(private readonly client: SupabaseClient<Database>) {}
 async createProject(params: {
   name: string;
   description?: string;
   accountId: string;
 }) {
   const { data, error } = await this.client
     .from('projects')
     .insert({
       name: params.name,
       description: params.description,
       account_id: params.accountId,
     })
     .select('id')
     .single();
   if (error) {
     throw error;
   }
   return data;
 }
 async getProjects(accountSlug: string) {
   const { data, error } = await this.client
     .from('projects')
     .select('*, account: account_id ! inner (slug)')
     .eq('account.slug', accountSlug);
   if (error) {
     throw error;
   }
   return data;
 }
 async getProjectMembers(projectId: string) {
   const { data, error } = await this.client
     .from('project_members')
     .select<
       string,
       Tables<'project_members'> & {
         account: {
           id: string;
           name: string;
           email: string;
         };
       }
     >('*, account: account_id ! inner (id, name, email)')
     .eq('project_id', projectId);
   if (error) {
     throw error;
   }
   return data;
 }
 async getProject(projectId: string) {
   const { data, error } = await this.client
     .from('projects')
     .select('*')
     .eq('id', projectId)
     .single();
   if (error) {
     throw error;
   }
   return data;
 }
 async hasPermission(params: { projectId: string; action: ProjectAction }) {
   const { data, error } = await this.client.rpc(
     'current_user_has_project_permission',
     {
       p_project_id: params.projectId,
       p_action: params.action,
     },
   );
   if (error) {
     throw error;
   }
   return data;
 }
 async addProjectMember(params: {
   projectId: string;
   userId: string;
   role?: 'member' | 'admin';
 }) {
   const { error } = await this.client.rpc('add_project_member', {
     p_project_id: params.projectId,
     p_user_id: params.userId,
     p_role: params.role ?? 'member',
   });
   if (error) {
     throw error;
   }
   return true;
 }
 async removeProjectMember(params: { projectId: string; userId: string }) {
   const { error } = await this.client.from('project_members').delete().match({
     project_id: params.projectId,
     account_id: params.userId,
   });
   if (error) {
     throw error;
   }
   return true;
 }
}

Let's look at some key methods:

  1. createProject: This method inserts a new project into the projects table.
  2. getProjects: Fetches all projects for a given account.
  3. getProjectMembers: Retrieves all members of a specific project.
  4. getProject: Fetches details of a single project.
  5. hasPermission: Checks if the current user has a specific permission for a project.
  6. addProjectMember: Adds a new member to a project.

These methods utilize the Supabase client to interact with the database, leveraging the SQL functions and RLS policies we set up earlier.

Using the ProjectsService in a Next.js Page

The ProjectsPage component demonstrates how to use the ProjectsService in a Next.js server component:

  1. We create an instance of ProjectsService using the Supabase client:
typescript
const client = getSupabaseServerComponentClient();
const service = createProjectsService(client);
  1. We fetch the projects for the current account:
typescript
const projects = use(service.getProjects(params.account));

The use function is a React hook that allows us to use async data in a synchronous-looking way in server components.

  1. We render the projects, showing an empty state if there are no projects:
tsx
<If condition={projects.length === 0}>
<EmptyState>
  {/* Empty state content */}
</EmptyState>
</If>
<div className={'grid grid-cols-1 gap-4 md:grid-cols-2 lg:grid-cols-4'}>
{projects.map((project) => (
  <CardButton key={project.id} asChild>
    <Link href={`/home/${params.account}/projects/${project.id}`}>
      {project.name}
    </Link>
  </CardButton>
))}
</div>

Here is a page that shows a list of projects for a given account:

apps/web/app/home/[account]/projects/page.tsx
import { use } from 'react';
import Link from 'next/link';
import { getSupabaseServerClient } from '@kit/supabase/server-client';
import { AppBreadcrumbs } from '@kit/ui/app-breadcrumbs';
import { Button } from '@kit/ui/button';
import {
 CardButton,
 CardButtonHeader,
 CardButtonTitle,
} from '@kit/ui/card-button';
import {
 EmptyState,
 EmptyStateButton,
 EmptyStateHeading,
 EmptyStateText,
} from '@kit/ui/empty-state';
import { If } from '@kit/ui/if';
import { PageBody, PageHeader } from '@kit/ui/page';
import { CreateProjectDialog } from '~/home/[account]/projects/_components/create-project-dialog';
import { createProjectsService } from '~/lib/server/projects/projects.service';
interface ProjectsPageProps {
 params: Promise<{
   account: string;
 }>;
}
export default function ProjectsPage({ params }: ProjectsPageProps) {
 const client = getSupabaseServerClient();
 const service = createProjectsService(client);
 const { account } = use(params);
 const projects = use(service.getProjects(account));
 return (
   <>
     <PageHeader title="Projects" description={<AppBreadcrumbs />}>
       <CreateProjectDialog>
         <Button>New Project</Button>
       </CreateProjectDialog>
     </PageHeader>
     <PageBody>
       <If condition={projects.length === 0}>
         <EmptyState>
           <EmptyStateHeading>No projects found</EmptyStateHeading>
           <EmptyStateText>
             You still have not created any projects. Create your first project
             now!
           </EmptyStateText>
           <CreateProjectDialog>
             <EmptyStateButton>Create Project</EmptyStateButton>
           </CreateProjectDialog>
         </EmptyState>
       </If>
       <div className={'grid grid-cols-1 gap-4 md:grid-cols-2 lg:grid-cols-4'}>
         {projects.map((project) => (
           <CardButton key={project.id} asChild>
             <Link href={`/home/${account}/projects/${project.id}`}>
               <CardButtonHeader>
                 <CardButtonTitle>{project.name}</CardButtonTitle>
               </CardButtonHeader>
             </Link>
           </CardButton>
         ))}
       </div>
     </PageBody>
   </>
 );
}

The page will display a list of projects for the current account, with a link to create a new project. If there are no projects, it will display an empty state with a button to create a new project, which is a link to the new project page.

Implementing Other Features

Based on the ProjectsService, you can implement other features:

  1. Creating a New Project: You could create a form that calls service.createProject() when submitted.
  2. Project Details Page: Use service.getProject() to fetch and display details of a single project.
  3. Managing Project Members: Use service.getProjectMembers() to list members, and service.addProjectMember() to add new ones.
  4. Permission Checking: Before performing actions, use service.hasPermission() to check if the user is allowed to do so.

Going beyond Projects

The reason why you're adding Projects is likely because yoyu want to group more entities into a projects, so to better organizate a team's work. You can extend the Projects feature to support other entities, such as Tasks, Documents, and more.

To do so, your project-related entities will need to be related to the Projects entity. This can be done using the project_id column in the related tables.

For example, if you want to add Tasks to a Project, you can create a new table tasks and add a foreign key to the project_id column in the projects table.

sql
-- Table: public.tasks
CREATE TABLE if not exists public.tasks (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  name text NOT NULL,
  description text,
  project_id uuid NOT NULL REFERENCES public.projects(id) ON DELETE CASCADE,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW()
);

With this setup, you can now create Tasks that are related to a Project. You can also use the Supabase client to interact with the database, leveraging the SQL functions and RLS policies we set up earlier.

Remember, the database schema and functions we've created provide a solid foundation for these features. As you build out your UI, you'll be calling the methods in ProjectsService to interact with the database in a secure, permission-controlled way.

This approach allows you to create a robust, scalable application while keeping your business logic cleanly separated from your UI components.

RLS Policies

When it comes to RLS, you can use the permissions system we've set up to control access to your database.

This allows you to create fine-grained access control for your users, ensuring that only those who should have access to certain data can do so.

sql
create policy select_projects_tasks
  on public.tasks
  for select
  to authenticated
  using (
    public.is_project_member(project_id)
  );

In short, if a user can view a Project, they can also view the Tasks related to that Project.

You can extend the permissions system further to allow for more granular access control based on the specific requirements of your application, such as can_edit_task or can_delete_task.

Best Practices

  1. Error Handling: The service methods throw errors when something goes wrong. Make sure to catch and handle these errors appropriately in your components.
  2. Type Safety: The service uses TypeScript, leveraging the Database type to ensure type safety when interacting with Supabase.
  3. Separation of Concerns: By using a service layer, we keep our database logic separate from our UI components, making the code more maintainable and testable.
  4. Server Components: By using Next.js server components, we can fetch data on the server, reducing the amount of JavaScript sent to the client and improving initial page load times.
  5. Reusability: The ProjectsService can be used across different parts of your application, promoting code reuse.

Full Schema

Below is the full schema:

sql
-- enums for project roles: owner, admin, member
create type public.project_role as enum ('owner', 'admin', 'member');
-- enums for project actions
create type public.project_action as enum (
  'view_project',
  'edit_project',
  'delete_project',
  'invite_member',
  'remove_member'
);
/*
# public.projects
# This table stores the projects for the account
*/
create table if not exists public.projects (
  id uuid default gen_random_uuid() primary key,
  name varchar(255) not null,
  description text,
  account_id uuid not null references public.accounts(id) on delete cascade,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
-- revoke access by default to public.projects
revoke all on public.projects from public, service_role;
-- grant access to authenticated users
grant select, insert, update, delete on public.projects to authenticated;
-- indexes on public.projects
create index projects_account_id on public.projects (account_id);
-- RLS policies on public.projects
alter table public.projects enable row level security;
/*
# public.project_members
# This table stores the members of a project
*/
create table if not exists public.project_members (
  project_id uuid not null references public.projects(id) on delete cascade,
  account_id uuid not null references public.accounts(id) on delete cascade,
  role public.project_role not null default 'member',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  primary key (project_id, account_id)
);
-- make sure there is only one owner per project
create unique index projects_unique_owner on public.project_members (project_id) where role = 'owner';
-- indexes on public.project_members
create index project_members_project_id on public.project_members (project_id);
create index project_members_account_id on public.project_members (account_id);
-- revoke access by default to public.project_members
revoke all on public.project_members from public, service_role;
-- grant access to authenticated users to public.project_members
grant select, insert, update, delete on public.project_members to authenticated;
-- RLS policies on public.project_members
alter table public.project_members enable row level security;
-- public.is_project_member
-- this function checks if a user is a member of a specific project
create or replace function public.is_project_member(p_project_id uuid)
returns boolean
set search_path = ''
as $$
  select exists (
    select 1 from public.project_members
    where project_id = p_project_id
    and account_id = (select auth.uid())
  );
$$ language sql security definer;
grant execute on function public.is_project_member(uuid) to authenticated;
-- public.is_project_admin
-- this function checks if a user is an admin or owner of a specific project
create or replace function public.is_project_admin(p_project_id uuid)
returns boolean
set search_path = ''
as $$
  select exists (
    select 1 from public.project_members
    where project_id = p_project_id
    and account_id = (select auth.uid())
    and role in ('owner', 'admin')
  );
$$ language sql;
grant execute on function public.is_project_admin to authenticated;
-- public.is_project_owner
-- check if a user is the owner of a project
create or replace function public.is_project_owner(project_id uuid)
returns boolean
set search_path = ''
as $$
  select exists (
    select 1 from public.project_members
    where project_id = $1
    and account_id = (select auth.uid())
    and role = 'owner'
  );
$$ language sql;
grant execute on function public.is_project_owner to authenticated;
-- public.user_has_project_permission
-- check if the current user has the required permissions to perform a specific action on a project
create or replace function public.user_has_project_permission(
  p_user_auth_id uuid,
  p_project_id uuid,
  p_action public.project_action
)
returns boolean
set search_path = ''
as $$
declare
  v_role public.project_role;
begin
  -- first, check if the user is a member of the project
  select role into v_role
  from public.project_members
  where project_id = p_project_id and account_id = p_user_auth_id;
  if v_role is null then
    return false;
  end if;
  -- check permissions based on role and action
  case v_role
    when 'owner' then
      return true;  -- owners can do everything
    when 'admin' then
      return p_action != 'delete_project';  -- admins can do everything except delete the project
    when 'member' then
      return p_action in ('view_project');
    else
      raise exception 'user must be a member of the project to perform this action';
  end case;
end;
$$ language plpgsql;
grant execute on function public.user_has_project_permission to authenticated;
-- public.current_user_has_project_permission
create or replace function public.current_user_has_project_permission(
  p_project_id uuid,
  p_action public.project_action
)
returns boolean
set search_path = ''
as $$
begin
  return public.user_has_project_permission((select auth.uid()), p_project_id, p_action);
end;
$$ language plpgsql;
grant execute on function public.current_user_has_project_permission to authenticated;
-- public.current_user_can_manage_project_member
-- Function to check if a user can manage another user in a project
create or replace function public.current_user_can_manage_project_member(
  p_target_member_role public.project_role,
  p_project_id uuid
)
returns boolean
set search_path = ''
as $$
declare
  v_current_user_role public.project_role;
begin
  select role into v_current_user_role
  from public.project_members
  where project_id = p_project_id and account_id = (select auth.uid());
  if v_current_user_role is null or p_target_member_role is null then
    raise exception 'User not found';
  end if;
  -- Check if the manager has a higher role
  return (v_current_user_role = 'owner' and p_target_member_role != 'owner') or
         (v_current_user_role = 'admin' and p_target_member_role = 'member');
end;
$$ language plpgsql;
grant execute on function public.current_user_can_manage_project_member to authenticated;
-- public.update_project_member_role
-- function to update the role of a project member
create or replace function public.update_project_member_role(
  p_user_id uuid,
  p_new_role public.project_role,
  p_project_id uuid
)
returns boolean
set search_path = ''
as $$
declare
  v_current_role public.project_role;
begin
  -- Get the current role of the member
  select role into v_current_role
  from public.project_members
  where project_id = p_project_id and account_id = p_user_id;
  -- Check if the manager can manage this member
  if not public.current_user_can_manage_project_member(v_current_role, p_project_id) then
    raise exception 'Permission denied';
  end if;
  if p_new_role = 'owner' then
    raise exception 'Owner cannot be updated to a different role';
  end if;
  -- Update the member's role
  update public.project_members
  set role = p_new_role
  where project_id = p_project_id and account_id = p_user_id;
  return true;
end;
$$ language plpgsql;
grant execute on function public.update_project_member_role to authenticated;
-- public.can_edit_project
-- check if the user can edit the project
create or replace function public.can_edit_project(p_user_auth_id uuid, p_project_id uuid)
returns boolean
set search_path = ''
as $$
  select public.user_has_project_permission(p_user_auth_id, p_project_id, 'edit_project'::public.project_action);
$$ language sql;
grant execute on function public.can_edit_project to authenticated;
-- public.can_delete_project
-- check if the user can delete the project
create or replace function public.can_delete_project(p_user_auth_id uuid, p_project_id uuid)
returns boolean
set search_path = ''
as $$
  select public.user_has_project_permission(p_user_auth_id, p_project_id, 'delete_project'::public.project_action);
$$ language sql;
grant execute on function public.can_delete_project to authenticated;
-- public.can_invite_project_member
-- check if the user can invite a new member to the project
create or replace function public.can_invite_project_member(p_user_auth_id uuid, p_project_id uuid)
returns boolean
set search_path = ''
as $$
  select public.user_has_project_permission(p_user_auth_id, p_project_id, 'invite_member'::public.project_action);
$$ language sql;
grant execute on function public.can_invite_project_member to authenticated;
/*
RLS POLICIES
*/
-- SELECT(public.projects)
create policy select_projects
  on public.projects
  for select
  to authenticated
  using (
    public.is_project_member(id)
  );
-- INSERT(public.projects)
create policy insert_new_project
  on public.projects
  for insert
  to authenticated
  with check (
    public.has_role_on_account(account_id)
  );
-- DELETE(public.projects)
create policy delete_project
  on public.projects
  for delete
  to authenticated
  using (
    public.can_delete_project((select auth.uid()), id)
  );
-- UPDATE(public.projects)
create policy update_project
  on public.projects
  for update
  to authenticated
  using (
    public.can_edit_project((select auth.uid()), id)
  )
  with check (
    public.can_edit_project((select auth.uid()), id)
  );
-- SELECT(public.project_members)
create policy select_project_members
  on public.project_members
  for select
  to authenticated
  using (
    public.is_project_member(project_id)
  );
-- INSERT(public.project_members)
create policy insert_project_member
  on public.project_members
  for insert
  to authenticated
  with check (
    public.can_invite_project_member(
      (select auth.uid()),
      project_id
    )
  );
-- UPDATE(public.project_members)
create policy update_project_members
  on public.project_members
  for update
  to authenticated
  using (
    public.current_user_can_manage_project_member(
      role,
      project_id
    )
  )
  with check (
    public.current_user_can_manage_project_member(
      role,
      project_id
    )
  );
-- DELETE(public.project_members)
create policy delete_project_members
  on public.project_members
  for delete
  to authenticated
  using (
    public.current_user_can_manage_project_member(
      role,
      project_id
    )
  );
/*
# FUNCTIONS
*/
-- function to add owner of the project creator as the first project member
create or replace function kit.add_project_owner()
returns trigger
as $$
begin
  insert into public.project_members (project_id, account_id, role)
  values (new.id, auth.uid(), 'owner'::public.project_role);
  return new;
end;
$$ language plpgsql security definer;
-- trigger to add owner of the project creator as the first project member
create trigger add_project_owner_on_insert
    after insert on public.projects
    for each row
    execute procedure kit.add_project_owner();
create or replace function public.add_project_member(
  p_project_id uuid,
  p_user_id uuid,
  p_role public.project_role default 'member'
) returns boolean
set search_path = ''
as $$
declare
  v_account_id uuid;
  v_is_personal_account boolean;
begin
  -- get the account_id for the project
  select account_id into v_account_id
  from public.projects
  where id = p_project_id;
  -- check if the target user account is a personal account (not a team account)
  -- Use security definer context to bypass RLS for this validation
  select is_personal_account into v_is_personal_account
  from public.accounts
  where id = p_user_id;
  if v_is_personal_account is null then
    raise exception 'user account not found';
  end if;
  if not v_is_personal_account then
    raise exception 'cannot invite team accounts to projects - only individual users can be invited';
  end if;
  -- check if the current user has permission to add members
  if not public.is_project_admin(p_project_id) or p_role = 'owner' then
    raise exception 'permission denied';
  end if;
  -- check if the user is a member of the team account
  if not exists (
    select 1 from public.accounts_memberships
    where account_id = v_account_id and user_id = p_user_id
  ) then
    raise exception 'user is not a member of the team account';
  end if;
  -- add the new member (the trigger will enforce the team membership check)
  insert into public.project_members (project_id, account_id, role)
  values (p_project_id, p_user_id, p_role)
  on conflict (project_id, account_id) do update
  set role = excluded.role;
  return true;
end;
$$ language plpgsql;
grant execute on function public.add_project_member to authenticated;
-- TRIGGERS on public.project_members
-- this trigger function ensures that a user being added to a project
-- is already a member of the associated team account and is a personal account
create or replace function kit.check_project_member_in_team()
returns trigger
as $$
declare
  v_account_id uuid;
  v_is_personal_account boolean;
begin
  select account_id from public.projects
  where id = new.project_id
  into v_account_id;
  -- check if the account being added is a personal account (not a team account)
  select is_personal_account into v_is_personal_account
  from public.accounts
  where id = new.account_id;
  if v_is_personal_account is null then
    raise exception 'account not found';
  end if;
  if not v_is_personal_account then
    raise exception 'cannot add team accounts to projects - only individual user accounts can be project members';
  end if;
  if not exists (
    select 1 from public.accounts_memberships
    where account_id = v_account_id and new.account_id = public.accounts_memberships.user_id
  ) then
    raise exception 'user must be a member of the team account to be added to the project';
  end if;
  return new;
end;
$$ language plpgsql security definer;
-- we create a trigger that uses the above function
create trigger ensure_project_member_in_team
before insert or update on public.project_members
for each row execute function kit.check_project_member_in_team();
create or replace function public.get_account_members_by_query(
  p_account_id uuid,
  p_query text
)
returns table (
  account_id uuid,
  user_id uuid,
  id uuid,
  name varchar(255),
  email varchar(255),
  picture_url varchar(1000)
) language plpgsql
set search_path = ''
as $$
begin
    return QUERY select
        am.account_id,
        am.user_id,
        acc.id,
        acc.name,
        acc.email,
        acc.picture_url
    from
        public.accounts_memberships am
        join public.accounts a on a.id = am.account_id
        join public.accounts acc on acc.id = am.user_id
    where
        am.account_id = p_account_id and
        (to_tsvector(acc.name || ' ' || acc.email) @@ plainto_tsquery(p_query))
        and am.user_id != (select auth.uid())
        and acc.is_personal_account = true; -- only return personal accounts, not team accounts
end
$$;
grant execute on function public.get_account_members_by_query to authenticated, service_role;

And here are the tests for the above schema:

sql
BEGIN;
create extension "basejump-supabase_test_helpers" version '0.0.6';
select
  no_plan ();
--- Create users which will automatically create personal accounts via triggers
select
  tests.create_supabase_user ('primary_owner', 'project-test-owner@example.com');
select
  tests.create_supabase_user ('admin', 'project-test-admin@example.com');
select
  tests.create_supabase_user ('member', 'project-test-member@example.com');
select
  tests.create_supabase_user ('custom', 'project-test-custom@example.com');
-- Authenticate as primary_owner to trigger the personal account creation
select makerkit.authenticate_as('primary_owner');
-- Wait for personal accounts to be created by the triggers
select pg_sleep(0.2);
-- Also authenticate as each user to trigger their personal account creation
select makerkit.authenticate_as('admin');
select pg_sleep(0.1);
select makerkit.authenticate_as('member');
select pg_sleep(0.1);
select makerkit.authenticate_as('custom');
select pg_sleep(0.1);
-- Switch to elevated permissions to set up team memberships
reset role;
-- Add all test users to the team account so they can be invited to projects
INSERT INTO public.accounts_memberships (account_id, user_id, account_role, created_at, updated_at)
VALUES
  ((select makerkit.get_account_id_by_slug('makerkit')), tests.get_supabase_uid('primary_owner'), 'owner', now(), now()),
  ((select makerkit.get_account_id_by_slug('makerkit')), tests.get_supabase_uid('admin'), 'member', now(), now()),
  ((select makerkit.get_account_id_by_slug('makerkit')), tests.get_supabase_uid('member'), 'member', now(), now()),
  ((select makerkit.get_account_id_by_slug('makerkit')), tests.get_supabase_uid('custom'), 'member', now(), now())
ON CONFLICT (user_id, account_id) DO UPDATE SET
  account_role = EXCLUDED.account_role;
create
or replace function tests.get_project_uuid () returns uuid as $$
  -- return a valid UUID
  select 'd8e789e4-8425-494b-9986-ad88ef430382'::uuid;
$$ language sql immutable;
select
  makerkit.authenticate_as ('primary_owner');
-- Create projects as an authenticated user so the trigger can work properly
INSERT INTO
  public.projects (id, name, description, account_id)
VALUES
  (
    tests.get_project_uuid (),
    'Test Project 1',
    'Description 1',
    (
      select
        makerkit.get_account_id_by_slug ('makerkit')
    )
  ),
  (
    'd0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
    'Test Project 2',
    'Description 2',
    (
      select
        makerkit.get_account_id_by_slug ('makerkit')
    )
  );
select
  makerkit.authenticate_as ('primary_owner');
set
  local role postgres;
select
  lives_ok (
    $$ INSERT INTO public.project_members (project_id, account_id, role)
   VALUES
     (
       tests.get_project_uuid (),
       tests.get_supabase_uid ('member'),
       'member'
     ); $$,
    'Inserting a user should create a personal account when personal accounts are enabled'
  );
select
  throws_ok (
    $$ INSERT INTO public.project_members (project_id, account_id, role)
        values (
            tests.get_project_uuid (),
            tests.get_supabase_uid ('admin'),
           'owner'
        ) $$,
    'duplicate key value violates unique constraint "projects_unique_owner"',
    'A project can only have one owner'
  );
select
  lives_ok (
    $$ INSERT INTO public.project_members (project_id, account_id, role)
        values (
            tests.get_project_uuid (),
            tests.get_supabase_uid ('admin'),
           'admin'
        ) $$,
    'A project can add an admin'
  );
select
  makerkit.authenticate_as ('primary_owner');
select
  is (
    (
      select
        count(*)
      from
        public.project_members
      where
        project_id = tests.get_project_uuid ()
    )::int,
    3,
    'The primary owner should be able to see the other members'
  );
-- Test is_project_member function
SELECT
  lives_ok (
    $$ select public.is_project_member (tests.get_project_uuid ()); $$,
    'is_project_member should return true for existing member'
  );
select
  makerkit.authenticate_as ('custom');
SELECT
  lives_ok (
    $$ select not public.is_project_member (tests.get_project_uuid ()); $$,
    'is_project_member should return false for non-member'
  );
select
  makerkit.authenticate_as ('primary_owner');
-- Test is_project_admin function
SELECT
  lives_ok (
    $$ select public.is_project_admin (tests.get_project_uuid ()); $$,
    'is_project_admin should return true for admin'
  );
select
  makerkit.authenticate_as ('admin');
select
  lives_ok (
    $$ select public.is_project_admin (tests.get_project_uuid ()); $$,
    'is_project_admin should return true for owner'
  );
select
  makerkit.authenticate_as ('member');
SELECT
  lives_ok (
    $$
    select NOT public.is_project_admin (tests.get_project_uuid ())$$,
    'is_project_admin should return false for non-admin'
  );
select
  makerkit.authenticate_as ('primary_owner');
-- Test user_has_project_permission function
SELECT
  lives_ok (
    $$ select public.user_has_project_permission (
      tests.get_supabase_uid ('primary_owner'),
      tests.get_project_uuid (),
      'delete_project'::project_action)
    $$,
    'Owner should have delete_project permission'
  );
select
  makerkit.authenticate_as ('admin');
SELECT
  lives_ok (
    $$ select public.user_has_project_permission (
      tests.get_supabase_uid ('admin'),
      tests.get_project_uuid (),
      'edit_project'::project_action) $$,
    'Admin should have edit_project permission'
  );
select
  makerkit.authenticate_as ('member');
SELECT
  lives_ok (
    $$ select NOT public.user_has_project_permission (
      tests.get_supabase_uid ('member'),
      tests.get_project_uuid (),
      'delete_project'::project_action)
    $$,
    'Member should not have delete_project permission'
  );
select
  makerkit.authenticate_as ('primary_owner');
select
  isnt_empty (
    $$ select * from public.project_members where project_id = tests.get_project_uuid() $$,
    'The primary owner should be able to see the other members'
  );
select
  makerkit.authenticate_as ('primary_owner');
-- Test update_project_member_role function
SELECT
  lives_ok (
    $$ SELECT public.update_project_member_role(
       tests.get_supabase_uid('member'),
        'admin'::project_role,
        tests.get_project_uuid()
     ) $$,
    'Owner should be able to update member role to admin'
  );
select
  makerkit.authenticate_as ('primary_owner');
select
  isnt_empty (
    $$ select * from public.project_members
     where project_id = tests.get_project_uuid()
  $$,
    'Primary owner should be able to see members'
  );
SELECT
  lives_ok (
    $$ delete from public.project_members
       where account_id = tests.get_supabase_uid('member') and project_id = tests.get_project_uuid();
    $$,
    'Owner should be able to remove a member'
  );
select
  is_empty (
    $$ select * from public.project_members where project_id = tests.get_project_uuid() and account_id = tests.get_supabase_uid('member') $$,
    'The primary owner has removed the member'
  );
-- reinsert member
insert into
  public.project_members (project_id, account_id, role)
values
  (
    tests.get_project_uuid (),
    tests.get_supabase_uid ('member'),
    'member'
  );
select
  makerkit.authenticate_as ('admin');
-- Test add_project_member function
SELECT
  throws_ok (
    $$SELECT public.add_project_member(tests.get_project_uuid(), tests.get_supabase_uid('member'), 'owner'::public.project_role)$$,
    'permission denied',
    'Should not be able to add a member with owner role'
  );
SELECT
  lives_ok (
    $$SELECT public.add_project_member(tests.get_project_uuid(), tests.get_supabase_uid('custom'), 'member'::public.project_role)$$,
    'Admin should be able to add a new member'
  );
-- Additional tests for is_project_member function
SELECT
  lives_ok (
    $$ select NOT public.is_project_member ('d0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11') $$,
    'is_project_member should return false for a project the user is not a member of'
  );
select
  makerkit.authenticate_as ('primary_owner');
-- Test is_project_owner function
SELECT
  lives_ok (
    $$ select public.is_project_owner (tests.get_project_uuid ())$$,
    'is_project_owner should return true for the project owner'
  );
select
  makerkit.authenticate_as ('admin');
SELECT
  lives_ok (
    $$ select NOT public.is_project_owner (tests.get_project_uuid ())$$,
    'is_project_owner should return false for non-owner (admin)'
  );
select
  makerkit.authenticate_as ('member');
SELECT
  lives_ok (
    $$ select NOT public.is_project_owner (tests.get_project_uuid ())$$,
    'is_project_owner should return false for non-owner (member)'
  );
select
  makerkit.authenticate_as ('primary_owner');
-- Additional tests for user_has_project_permission function
SELECT
  lives_ok (
    $$ select public.user_has_project_permission (
      tests.get_supabase_uid ('primary_owner'),
      tests.get_project_uuid (),
      'invite_member'::project_action
    ) $$,
    'Owner should have invite_member permission'
  );
SELECT
  lives_ok (
    $$ select public.user_has_project_permission (
      tests.get_supabase_uid ('primary_owner'),
      tests.get_project_uuid (),
      'remove_member'::project_action
    )$$,
    'Owner should have remove_member permission'
  );
select
  makerkit.authenticate_as ('admin');
SELECT
  lives_ok (
    $$ select public.user_has_project_permission (
      tests.get_supabase_uid ('admin'),
      tests.get_project_uuid (),
      'invite_member'::project_action
    ) $$,
    'Admin should have invite_member permission'
  );
SELECT
  lives_ok (
    $$ select NOT public.user_has_project_permission (
      tests.get_supabase_uid ('admin'),
      tests.get_project_uuid (),
      'delete_project'::project_action
    )$$,
    'Admin should not have delete_project permission'
  );
select
  makerkit.authenticate_as ('member');
SELECT
  lives_ok (
    $$ select public.user_has_project_permission (
      tests.get_supabase_uid ('member'),
      tests.get_project_uuid (),
      'view_project'::project_action
    )$$,
    'Member should have view_project permission'
  );
SELECT
  lives_ok (
    $$ select NOT public.user_has_project_permission (
      tests.get_supabase_uid ('member'),
      tests.get_project_uuid (),
      'invite_member'::project_action
    )$$,
    'Member should not have invite_member permission'
  );
select
  makerkit.authenticate_as ('primary_owner');
-- Test add_project_member function
SELECT
  lives_ok (
    $$SELECT public.add_project_member(tests.get_project_uuid(), tests.get_supabase_uid('custom'), 'member'::public.project_role)$$,
    'Owner should be able to add a new member'
  );
select
  makerkit.authenticate_as ('admin');
SELECT
  lives_ok (
    $$SELECT public.add_project_member(tests.get_project_uuid(), tests.get_supabase_uid('custom'), 'member'::public.project_role)$$,
    'Admin should be able to add a new member'
  );
SELECT
  throws_ok (
    $$SELECT public.add_project_member(tests.get_project_uuid(), tests.get_supabase_uid('custom'), 'owner'::public.project_role)$$,
    'permission denied',
    'Admin should not be able to add a member with owner role'
  );
select
  makerkit.authenticate_as ('member');
SELECT
  throws_ok (
    $$SELECT public.add_project_member(tests.get_project_uuid(), tests.get_supabase_uid('custom'), 'member'::public.project_role)$$,
    'permission denied',
    'Member should not be able to add a new member'
  );
select
  makerkit.authenticate_as ('primary_owner');
-- Test RLS policies
SELECT
  results_eq (
    $$SELECT count(*) FROM public.projects$$,
    ARRAY[2::bigint],
    'Owner should see all projects'
  );
select
  makerkit.authenticate_as ('admin');
SELECT
  results_eq (
    $$SELECT count(*) FROM public.projects$$,
    ARRAY[1::bigint],
    'Member should only see projects they are a member of'
  );
select
  tests.create_supabase_user ('foreigner', 'foreigner@test.com');
select
  makerkit.authenticate_as ('foreigner');
SELECT
  results_eq (
    $$SELECT count(*) FROM public.projects$$,
    ARRAY[0::bigint],
    'Non-member should not see any projects'
  );
select
  makerkit.authenticate_as ('primary_owner');
-- Testing direct updates for RLS policies
select
  makerkit.authenticate_as ('primary_owner');
select
  isnt_empty (
    $$ select * from public.project_members where project_id = tests.get_project_uuid() $$,
    'The primary owner should be able to see the other members'
  );
select
  makerkit.authenticate_as ('admin');
select
  isnt_empty (
    $$ select * from public.project_members where project_id = tests.get_project_uuid() $$,
    'The owner should be able to see the other members'
  );
select
  makerkit.authenticate_as ('member');
select
  isnt_empty (
    $$ select * from public.project_members where project_id = tests.get_project_uuid() $$,
    'The member should be able to see the other members'
  );
select
  makerkit.authenticate_as ('custom');
select
  isnt_empty (
    $$ select * from public.project_members where project_id = tests.get_project_uuid() $$,
    'The custom user should be able to see the other members'
  );
select
  makerkit.authenticate_as ('foreigner');
select
  is_empty (
    $$ select * from public.project_members where project_id = tests.get_project_uuid() $$,
    'The foreigner should not be able to see the other members'
  );
-- try to update a role
select
  makerkit.authenticate_as ('primary_owner');
select
  row_eq (
    $$ select account_id, role from public.project_members where project_id = tests.get_project_uuid() $$,
    row (
      tests.get_supabase_uid ('primary_owner'),
      'owner'::public.project_role
    ),
    'The primary owner should be able to see the other members'
  );
SELECT
  lives_ok (
    $$ update public.project_members set role = 'admin' where project_id = tests.get_project_uuid() and account_id = tests.get_supabase_uid('member') $$,
    'Owner should be able to update a member role'
  );
SELECT
  row_eq (
    $$ select role from public.project_members where project_id = tests.get_project_uuid() and account_id = tests.get_supabase_uid('member') $$,
    row ('admin'::public.project_role),
    'Owner has updated member role'
  );
-- revert role to member
update public.project_members
set role = 'member'
where
  project_id = tests.get_project_uuid ()
  and account_id = tests.get_supabase_uid ('member');
-- Test update project
select
  lives_ok (
    $$ update public.projects set name = 'Updated Project' where id = tests.get_project_uuid() $$,
    'Owner should be able to update a project'
  );
select
  row_eq (
    $$ select name from public.projects where id = tests.get_project_uuid() $$,
    row ('Updated Project'::varchar),
    'Owner has updated project name'
  );
-- Test update project as member'
select
  makerkit.authenticate_as ('member');
select
  lives_ok (
    $$ update public.projects set name = 'Updated Project 2' where id = tests.get_project_uuid() $$,
    'Failing Updates are silently ignored'
  );
select
  row_eq (
    $$ select name from public.projects where id = tests.get_project_uuid() $$,
    row ('Updated Project'::varchar),
    'Member has not updated project name'
  );
-- Test cascading updates
select
  makerkit.authenticate_as ('primary_owner');
SELECT
  lives_ok (
    $$DELETE FROM public.projects WHERE id = tests.get_project_uuid()$$,
    'Owner should be able to delete a project'
  );
SELECT
  results_eq (
    $$SELECT count(*) FROM public.project_members WHERE project_id = tests.get_project_uuid()$$,
    ARRAY[0::bigint],
    'Project members should be deleted when project is deleted'
  );
-- Additional tests for account type validation
select
  makerkit.authenticate_as ('primary_owner');
-- Create a team account for testing
reset role;
INSERT INTO
  public.accounts (id, name, slug, is_personal_account, primary_owner_user_id)
VALUES
  (
    '11111111-1111-1111-1111-111111111111',
    'Test Team Account',
    'test-team',
    false,
    tests.get_supabase_uid ('primary_owner')
  );
-- Note: We don't add the team account to memberships because:
-- 1. It would violate foreign key constraints (team account UUID not in auth.users)
-- 2. Team accounts should never be members of other accounts anyway
-- The function validation will catch the account type before membership validation
-- Reset to authenticated role for testing
select
  makerkit.authenticate_as ('primary_owner');
-- Try to add a team account to project members (should fail via trigger)
set
  local role postgres;
SELECT
  throws_ok (
    $$ INSERT INTO public.project_members (project_id, account_id, role)
       VALUES (
         tests.get_project_uuid(),
         '11111111-1111-1111-1111-111111111111',
         'member'
       ) $$,
    'cannot add team accounts to projects - only individual user accounts can be project members',
    'Should not be able to add team accounts as project members via trigger'
  );
-- Reset role for function testing
reset role;
select
  makerkit.authenticate_as ('primary_owner');
-- Test add_project_member function with team account (should fail)
SELECT
  throws_ok (
    $$ SELECT public.add_project_member(
         tests.get_project_uuid(),
         '11111111-1111-1111-1111-111111111111',
         'member'::public.project_role
       ) $$,
    'cannot invite team accounts to projects - only individual users can be invited',
    'Should not be able to add team accounts via add_project_member function'
  );
-- Test with non-existent account (should fail)
SELECT
  throws_ok (
    $$ SELECT public.add_project_member(
         tests.get_project_uuid(),
         '22222222-2222-2222-2222-222222222222',
         'member'::public.project_role
       ) $$,
    'user account not found',
    'Should fail when trying to add non-existent account'
  );
-- Test get_account_members_by_query excludes team accounts
SELECT
  is_empty (
    $$ SELECT * FROM public.get_account_members_by_query(
         (select makerkit.get_account_id_by_slug('makerkit')),
         'Test Team Account'
       ) $$,
    'get_account_members_by_query should not return team accounts'
  );
-- Test that personal accounts appear in query results
SELECT
  isnt_empty (
    $$ SELECT * FROM public.get_account_members_by_query(
         (select makerkit.get_account_id_by_slug('makerkit')),
         'custom'
       ) $$,
    'get_account_members_by_query should return personal accounts'
  );
-- Test add_project_member validation for user not in team
select
  tests.create_supabase_user ('outsider', 'outsider@test.com');
-- Authenticate as outsider to trigger personal account creation
select makerkit.authenticate_as('outsider');
-- Go back to primary_owner for the test
select makerkit.authenticate_as('primary_owner');
SELECT
  throws_ilike (
    $$ SELECT public.add_project_member(
         tests.get_project_uuid(),
         tests.get_supabase_uid('outsider'),
         'member'::public.project_role
       ) $$,
    'user account not found',
    'User is not a member of the team account'
  );
-- Finish the tests
SELECT
  *
FROM
  finish ();
ROLLBACK;

User Interface

Now that we have a data model, let's create the UI for managing projects. We will create a new page at app/dashboard/[account]/projects/page.tsx that will display a list of projects for the current account.

Adding the Page to the Navigation

Let's update the navigation menu to add a new link to the projects page. We will create a new file at apps/web/config/team-account-navigation.config.tsx with the following content:

apps/web/config/team-account-navigation.config.tsx
{
 label: 'common:routes.dashboard',
 path: pathsConfig.app.accountHome.replace('[account]', account),
 Icon: <LayoutDashboard className={iconClasses} />,
 end: true,
},
{
 label: 'common:routes.projects',
 path: `/home/${account}/projects`,
 Icon: <FolderKanban className={iconClasses} />,
},

NB: you need to the component FolderKanban from lucide-react to use the icon.

Creating the Projects List Page

We can now create the page at app/dashboard/[account]/projects/page.tsx with the following content:

app/dashboard/[account]/projects/page.tsx
import { use } from 'react';
import Link from 'next/link';
import { getSupabaseServerClient } from '@kit/supabase/server-client';
import { AppBreadcrumbs } from '@kit/ui/app-breadcrumbs';
import { Button } from '@kit/ui/button';
import {
 CardButton,
 CardButtonHeader,
 CardButtonTitle,
} from '@kit/ui/card-button';
import {
 EmptyState,
 EmptyStateButton,
 EmptyStateHeading,
 EmptyStateText,
} from '@kit/ui/empty-state';
import { If } from '@kit/ui/if';
import { PageBody, PageHeader } from '@kit/ui/page';
import { CreateProjectDialog } from '~/home/[account]/projects/_components/create-project-dialog';
import { createProjectsService } from '~/lib/server/projects/projects.service';
interface ProjectsPageProps {
 params: Promise<{
   account: string;
 }>;
}
export default function ProjectsPage({ params }: ProjectsPageProps) {
 const client = getSupabaseServerClient();
 const service = createProjectsService(client);
 const { account } = use(params);
 const projects = use(service.getProjects(account));
 return (
   <>
     <PageHeader title="Projects" description={<AppBreadcrumbs />}>
       <CreateProjectDialog>
         <Button>New Project</Button>
       </CreateProjectDialog>
     </PageHeader>
     <PageBody>
       <If condition={projects.length === 0}>
         <EmptyState>
           <EmptyStateHeading>No projects found</EmptyStateHeading>
           <EmptyStateText>
             You still have not created any projects. Create your first project
             now!
           </EmptyStateText>
           <CreateProjectDialog>
             <EmptyStateButton>Create Project</EmptyStateButton>
           </CreateProjectDialog>
         </EmptyState>
       </If>
       <div className={'grid grid-cols-1 gap-4 md:grid-cols-2 lg:grid-cols-4'}>
         {projects.map((project) => (
           <CardButton key={project.id} asChild>
             <Link href={`/home/${account}/projects/${project.id}`}>
               <CardButtonHeader>
                 <CardButtonTitle>{project.name}</CardButtonTitle>
               </CardButtonHeader>
             </Link>
           </CardButton>
         ))}
       </div>
     </PageBody>
   </>
 );
}

This page is pretty straightforward, it just displays a list of projects for the account. We can now build the ProjectsDataTable component that will display the list of projects.

When we have a list of projects, we display them in the following way:

Instead, we can also display a message when there are no projects:

Creating the Dialog to Create a Project

We can now create the dialog to create a project. We will create a new file at apps/web/home/[account]/projects/_components/create-project-dialog.tsx with the following content:

apps/web/home/[account]/projects/_components/create-project-dialog.tsx
'use client';
import { useState, useTransition } from 'react';
import { zodResolver } from '@hookform/resolvers/zod';
import { useForm } from 'react-hook-form';
import { useTeamAccountWorkspace } from '@kit/team-accounts/hooks/use-team-account-workspace';
import { Button } from '@kit/ui/button';
import {
 Dialog,
 DialogContent,
 DialogDescription,
 DialogHeader,
 DialogTitle,
 DialogTrigger,
} from '@kit/ui/dialog';
import {
 Form,
 FormControl,
 FormDescription,
 FormField,
 FormItem,
 FormLabel,
} from '@kit/ui/form';
import { Input } from '@kit/ui/input';
import { CreateProjectSchema } from '../_lib/schema/create-project-schema';
import { createProjectAction } from '../_lib/server/server-actions';
export function CreateProjectDialog(props: React.PropsWithChildren) {
 const [isOpen, setIsOpen] = useState(false);
 return (
   <Dialog open={isOpen} onOpenChange={setIsOpen}>
     <DialogTrigger asChild>{props.children}</DialogTrigger>
     <DialogContent>
       <DialogHeader>
         <DialogTitle>Create Project</DialogTitle>
         <DialogDescription>
           Create a new project for your team.
         </DialogDescription>
       </DialogHeader>
       <CreateProjectDialogForm
         onCancel={() => setIsOpen(false)}
         onCreateProject={() => setIsOpen(false)}
       />
     </DialogContent>
   </Dialog>
 );
}
function CreateProjectDialogForm(props: {
 onCreateProject?: (name: string) => unknown;
 onCancel?: () => unknown;
}) {
 const {
   account: { id: accountId },
 } = useTeamAccountWorkspace();
 const form = useForm({
   resolver: zodResolver(CreateProjectSchema),
   defaultValues: {
     name: '',
     accountId,
   },
 });
 const [pending, startTransition] = useTransition();
 return (
   <Form {...form}>
     <form
       className={'flex flex-col space-y-4'}
       onSubmit={form.handleSubmit((data) => {
         startTransition(async () => {
           await createProjectAction(data);
         });
       })}
     >
       <FormField
         name={'name'}
         render={({ field }) => (
           <FormItem>
             <FormLabel>Project Name</FormLabel>
             <FormControl>
               <Input
                 data-test={'project-name-input'}
                 required
                 min={3}
                 max={50}
                 type={'text'}
                 placeholder={''}
                 {...field}
               />
             </FormControl>
             <FormDescription>Enter a name for your project (Ex. Accounting)</FormDescription>
           </FormItem>
         )}
       />
       <div className={'flex justify-end space-x-2'}>
         <Button variant={'outline'} type={'button'} onClick={props.onCancel}>
           Cancel
         </Button>
         <Button disabled={pending}>Create Project</Button>
       </div>
     </form>
   </Form>
 );
}

We can now create the server action to create a project. We will create a new file at apps/web/home/[account]/projects/_lib/server/server-actions.ts with the following content:

apps/web/home/[account]/projects/_lib/server/server-actions.ts
'use server';
import { revalidatePath } from 'next/cache';
import { enhanceAction } from '@kit/next/actions';
import { getLogger } from '@kit/shared/logger';
import { getSupabaseServerClient } from '@kit/supabase/server-client';
import { CreateProjectSchema } from '../schema/create-project-schema';
export const createProjectAction = enhanceAction(
 async (data) => {
   const client = getSupabaseServerClient();
   const logger = await getLogger();
   logger.info(
     {
       accountId: data.accountId,
       name: data.name,
     },
     'Creating project...',
   );
   const response = await client.from('projects').insert({
     account_id: data.accountId,
     name: data.name,
   });
   if (response.error) {
     logger.error(
       {
         accountId: data.accountId,
         name: data.name,
         error: response.error,
       },
       'Failed to create project',
     );
     throw response.error;
   }
   logger.info(
     {
       accountId: data.accountId,
       name: data.name,
     },
     'Project created',
   );
   revalidatePath('/home/[account]/projects', 'layout');
 },
 {
   schema: CreateProjectSchema,
   auth: true,
 },
);

When the user submits the form, we call the createProjectAction server action. This action will insert a new project into the database. To refresh the data, we use the revalidatePath function to revalidate the /home/[account]/projects path.

Project Detail Page

Now, we want to create a detail page for each project.

In this case, we will use an outer layout to wrap the project detail page, so that we can display an inner navigation menu that will reuse the same layout.

We will create a new file at apps/web/home/[account]/projects/[id]/layout.tsx with the following content:

tsx
import { use } from 'react';
import { AppBreadcrumbs } from '@kit/ui/app-breadcrumbs';
import {
  BorderedNavigationMenu,
  BorderedNavigationMenuItem,
} from '@kit/ui/bordered-navigation-menu';
import { PageBody, PageHeader } from '@kit/ui/page';
import { getProject } from './_lib/server/get-project';
interface ProjectDetailLayoutProps {
  params: Promise<{
    id: string;
    account: string;
  }>;
}
export default function ProjectDetailLayout({
  params,
  children,
}: React.PropsWithChildren<ProjectDetailLayoutProps>) {
  const { id, account } = use(params);
  const project = use(getProject(id));
  return (
    <>
      <PageHeader
        title={project.name}
        description={<AppBreadcrumbs values={{ [project.id]: project.name }} />}
      />
      <PageBody className={'space-y-4'}>
        <div className={'border-b px-4 pb-2.5'}>
          <BorderedNavigationMenu>
            <BorderedNavigationMenuItem
              path={`/home/${account}/projects/${project.id}`}
              label={'Documents'}
            />
            <BorderedNavigationMenuItem
              path={`/home/${account}/projects/${project.id}/members`}
              label={'Members'}
            />
          </BorderedNavigationMenu>
        </div>
        {children}
      </PageBody>
    </>
  );
}

Just as an example, we have added a navigation menu with two links: Documents and Members. You can customize this menu to fit your needs.

Using the "getProject" function

As you may have noticed, we have a getProject function that will fetch the project from the database.

We use this to make use of caching and reuse this function across all the pages in the Project layout, so we don't re-fetch the data from the database when avigating between pages.

apps/web/app/home/[account]/projects/[id]/_lib/server/get-project.ts
import { cache } from 'react';
import { getSupabaseServerClient } from '@kit/supabase/server-client';
import { createProjectsService } from '~/lib/server/projects/projects.service';
export const getProject = cache(projectLoader);
async function projectLoader(id: string) {
 const client = getSupabaseServerClient();
 const service = createProjectsService(client);
 return service.getProject(id);
}

Project Detail Page

Now, we want to create a detail page for each project.

We will create a new file at apps/web/app/home/[account]/projects/[id]/page.tsx with the following content:

apps/web/app/home/[account]/projects/[id]/page.tsx
interface ProjectDetailPageProps {
 params: Promise<{
   id: string;
   account: string;
 }>;
}
export default function ProjectDetailPage(_: ProjectDetailPageProps) {
 return <div className={'flex flex-col space-y-4'}>...</div>;
}

Generally speaking, it is up to you to decide how you want to display the project data. This is the inner part of the layout, so we can use the same layout for the detail page.

Conclusion

In this tutorial, we've covered how to create a simple (yet powerful) data model for managing Projects in your Makerkit application. We've also explored how to implement this data model using Supabase and Next.js, and how to use the Supabase client to interact with the database.

By following these steps, you can create a robust, scalable application that leverages the power of Supabase and Next.js to manage your team's projects in a secure, permission-controlled way.