Projects Data Model

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:


-- 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


-- 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:


-- 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:


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:


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.


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:


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 user_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:


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 user_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:


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 user_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:


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 user_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:


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 $$
DECLARE
  v_role public.project_role;
BEGIN
  SELECT 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:


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 user_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:


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 user_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 user_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:


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:


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:


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


-- SELECT
CREATE POLICY select_projects
  ON public.projects
  FOR SELECT
  TO authenticated
  USING (
    public.is_project_member(id)
  );
 
-- INSERT
CREATE POLICY insert_new_project
  ON public.projects
  FOR INSERT
  TO authenticated
  WITH CHECK (
    public.has_role_on_account(account_id)
  );
 
-- DELETE
CREATE POLICY delete_project
  ON public.projects
  FOR DELETE
  TO authenticated
  USING (
    public.can_delete_project((SELECT auth.uid()), id)
  );
 
-- UPDATE
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:

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.
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.
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.
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


-- SELECT
CREATE POLICY select_project_members
  ON public.project_members
  FOR SELECT
  TO authenticated
  USING (
    public.is_project_member(project_id)
  );
 
-- INSERT
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
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
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:

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.
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.
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.
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:


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:


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;
BEGIN
  -- 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;
 
  -- 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 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:


CREATE OR REPLACE FUNCTION kit.check_project_member_in_team()
RETURNS TRIGGER
AS $$
DECLARE
  v_account_id UUID;
BEGIN
  SELECT account_id FROM public.projects
  WHERE id = NEW.project_id
  INTO v_account_id;
 
  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.


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:


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:


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:


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:


-- 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 } 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('*')
      .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;
  }
}

Let's look at some key methods:

createProject: This method inserts a new project into the projects table.
getProjects: Fetches all projects for a given account.
getProjectMembers: Retrieves all members of a specific project.
getProject: Fetches details of a single project.
hasPermission: Checks if the current user has a specific permission for a project.
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:

We create an instance of ProjectsService using the Supabase client:


const client = getSupabaseServerComponentClient();
const service = createProjectsService(client);

We fetch the projects for the current account:
```
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.

We render the projects, showing an empty state if there are no projects:


<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 { getSupabaseServerComponentClient } from '@kit/supabase/server-component-client';
import { Button } from '@kit/ui/button';
import { CardButton } 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 { createProjectsService } from '~/lib/server/projects/projects.service';
 
interface ProjectsPageProps {
  params: {
    account: string;
  };
}
 
export default function ProjectsPage({ params }: ProjectsPageProps) {
  const client = getSupabaseServerComponentClient();
  const service = createProjectsService(client);
 
  const projects = use(service.getProjects(params.account));
 
  return (
    <>
      <PageHeader title="Projects" description="Manage your team's projects">
        <Link href={`/home/${params.account}/projects/new`}>
          <Button>New Project</Button>
        </Link>
      </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>
 
            <EmptyStateButton>Create Project</EmptyStateButton>
          </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>
      </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:

Creating a New Project: You could create a form that calls service.createProject() when submitted.
Project Details Page: Use service.getProject() to fetch and display details of a single project.
Managing Project Members: Use service.getProjectMembers() to list members, and service.addProjectMember() to add new ones.
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.


-- 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.


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

Error Handling: The service methods throw errors when something goes wrong. Make sure to catch and handle these errors appropriately in your components.
Type Safety: The service uses TypeScript, leveraging the Database type to ensure type safety when interacting with Supabase.
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.
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.
Reusability: The ProjectsService can be used across different parts of your application, promoting code reuse.

Full Schema

Below is the full schema:


-- 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,
  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)
);
 
-- 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_user_id on public.project_members (user_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 user_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 user_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 user_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 user_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 $$
declare
  v_role public.project_role;
begin
  select 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 user_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 user_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 user_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, 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();
 
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;
begin
  -- 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;
 
  -- 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 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;
 
-- 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
create or replace function kit.check_project_member_in_team()
returns trigger
as $$
declare
  v_account_id uuid;
begin
  select account_id from public.projects
  where id = new.project_id
  into v_account_id;
 
  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;
 
-- 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();

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.

PreviousCheckout Addons with Stripe Billing