Architecture & Database Schema

Complete database schema, relationships, and architecture patterns for the multi-tenant SaaS platform

This document provides a comprehensive overview of the database schema, relationships, and architectural patterns used in the platform.

Database Schema Overview

Core Tables

1. auth.users (Supabase Auth)

CREATE TABLE auth.users (
id uuid PRIMARY KEY,
email varchar(255) UNIQUE,
encrypted_password varchar(255),
email_confirmed_at timestamptz,
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
);

Purpose: User authentication and identity Managed by: Supabase Auth Relationships: One-to-many with accounts_memberships

2. accounts

CREATE TABLE public.accounts (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
name varchar(150) NOT NULL,
slug varchar(150) UNIQUE NOT NULL,
description varchar(280),
picture_url varchar(1000),
status varchar(20) CHECK (status IN ('active', 'inactive')),
primary_owner_user_id uuid NOT NULL REFERENCES auth.users(id),
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
);

CREATE INDEX idx_accounts_slug ON accounts(slug);
CREATE INDEX idx_accounts_primary_owner ON accounts(primary_owner_user_id);

Purpose: Team accounts (multi-tenant workspaces) Type: Both personal and team accounts Key Fields:

slug: URL-friendly identifier (globally unique)
status: Account status (active/inactive)
primary_owner_user_id: Account creator/owner

3. accounts_memberships

CREATE TABLE public.accounts_memberships (
id bigserial PRIMARY KEY,
user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
role varchar(50) NOT NULL,
created_at timestamptz DEFAULT now(),
UNIQUE(user_id, account_id)
);

CREATE INDEX idx_memberships_user ON accounts_memberships(user_id);
CREATE INDEX idx_memberships_account ON accounts_memberships(account_id);

Purpose: User membership in accounts Roles: owner, manager, member, readonly Constraints: One user can have one role per account (UNIQUE constraint)

4. projects

CREATE TABLE public.projects (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
name varchar(255) NOT NULL,
slug varchar(128) NOT NULL,
description text,
picture_url varchar(1000),
active boolean DEFAULT true,
primary_owner_user_id uuid NOT NULL REFERENCES auth.users(id),
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now(),
UNIQUE(account_id, slug)
);

CREATE INDEX idx_projects_account ON projects(account_id);
CREATE INDEX idx_projects_slug ON projects(account_id, slug);

Purpose: Projects within team accounts Key Fields:

slug: URL-friendly identifier (unique per account)
active: Project status
account_id: Parent team account

5. projects_members

CREATE TABLE public.projects_members (
id bigserial PRIMARY KEY,
user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
role varchar(50) NOT NULL,
created_at timestamptz DEFAULT now(),
UNIQUE(user_id, project_id)
);

CREATE INDEX idx_project_members_user ON projects_members(user_id);
CREATE INDEX idx_project_members_project ON projects_members(project_id);

Purpose: User membership in projects Roles: owner, manager, executor, investor, auditor, technical, marketing, member, readonly Note: User must be account member OR project member to access project

Entity Relationships

┌──────────────┐
│ auth.users │
└──────┬───────┘
│
│ 1:N
│
├─────────────────────────────────────┐
│ │
│ │
┌──────▼─────────────┐ ┌───────▼──────────┐
│ accounts (primary) │ │ accounts_members │
│ - owner │ │ - member │
└──────┬─────────────┘ └───────┬──────────┘
│ │
│ 1:N │
│ │
┌──────▼───────┐ ┌──────▼────────┐
│ projects │ │ projects │
│ │ │ (as member) │
└──────┬───────┘ └───────────────┘
│
│ 1:N
│
┌──────▼──────────┐
│ projects_members│
└─────────────────┘

Row Level Security (RLS)

Accounts RLS

-- Users can only see accounts they are members of
CREATE POLICY "Users can view their accounts"
ON accounts FOR SELECT
USING (
id IN (
SELECT account_id FROM accounts_memberships
WHERE user_id = auth.uid()
)
);

-- Only owners and managers can update
CREATE POLICY "Owners and managers can update accounts"
ON accounts FOR UPDATE
USING (
primary_owner_user_id = auth.uid()
OR
id IN (
SELECT account_id FROM accounts_memberships
WHERE user_id = auth.uid()
AND role IN ('owner', 'manager')
)
);

Projects RLS

-- Users can view projects if they're account or project members
CREATE POLICY "Users can view accessible projects"
ON projects FOR SELECT
USING (
-- Account member
account_id IN (
SELECT account_id FROM accounts_memberships
WHERE user_id = auth.uid()
)
OR
-- Project member
id IN (
SELECT project_id FROM projects_members
WHERE user_id = auth.uid()
)
);

-- Only project owners and managers can update
CREATE POLICY "Project owners and managers can update"
ON projects FOR UPDATE
USING (
id IN (
SELECT project_id FROM projects_members
WHERE user_id = auth.uid()
AND role IN ('owner', 'manager')
)
);

Database Functions

1. is_project_slug_unique

CREATE OR REPLACE FUNCTION is_project_slug_unique(
p_account_id uuid,
p_slug varchar,
p_project_id uuid DEFAULT NULL
)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
BEGIN
RETURN NOT EXISTS (
SELECT 1
FROM public.projects
WHERE account_id = p_account_id
AND slug = p_slug
AND (p_project_id IS NULL OR id != p_project_id)
);
END;
$$;

GRANT EXECUTE ON FUNCTION is_project_slug_unique TO authenticated;

Purpose: Validate project slug uniqueness within account Parameters:

p_account_id: Account to check within
p_slug: Slug to validate
p_project_id: Current project ID (for updates)

2. is_account_slug_unique

CREATE OR REPLACE FUNCTION is_account_slug_unique(
p_slug varchar,
p_account_id uuid DEFAULT NULL
)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
BEGIN
RETURN NOT EXISTS (
SELECT 1
FROM public.accounts
WHERE slug = p_slug
AND (p_account_id IS NULL OR id != p_account_id)
);
END;
$$;

GRANT EXECUTE ON FUNCTION is_account_slug_unique TO authenticated;

Purpose: Validate account slug uniqueness globally Parameters:

p_slug: Slug to validate
p_account_id: Current account ID (for updates)

Architecture Patterns

1. Server Components + Server Actions

// Page (Server Component)
async function ProjectSettingsPage({ params }) {
const { id } = await params;
const project = await loadProjectData(id);

return
<ProjectSettings project={project} />;
}

// Server Action
'use server';
export const updateProject = enhanceAction(
async (data) => {
const client = getSupabaseServerClient();
// RLS automatically enforces permissions
await client.from('projects').update(data).eq('id', data.id);
},
{ schema: ProjectSchema }
);

Benefits:

Automatic RLS enforcement
No API routes needed
Type-safe with Zod schemas
Automatic error handling

2. Loader Pattern

// Loader function (server-only)
import 'server-only';

export async function loadProjectSettingsPageData(
client: SupabaseClient,
projectId: string
) {
return Promise.all([
loadProject(client, projectId),
loadPermissions(client, projectId),
]);
}

// Used in page
const [project, permissions] = await loadProjectSettingsPageData(client, id);

Benefits:

Parallel data fetching
Server-only execution
Centralized data loading
Easy testing

3. Service Pattern

// Service layer
export class ProjectsService {
constructor(private client: SupabaseClient) {}

async createProject(data: CreateProjectData) {
const { data: project, error } = await this.client
.from('projects')
.insert(data)
.select()
.single();

if (error) return { error };
return { data: project };
}
}

// Usage in server action
const service = new ProjectsService(client);
const result = await service.createProject(data);

Benefits:

Reusable business logic
Testable
Encapsulation
Clear separation of concerns

4. Cache Revalidation

// Server action with revalidation
export const updateTeamAccount = enhanceAction(
async (params) => {
await updateAccount(params);

// Revalidate layout cache
revalidatePath(`/home/${params.slug}`, 'layout');

// Redirect if slug changed
if (slugChanged) {
redirect(`/home/${newSlug}/settings`);
}
}
);

// Client component
const router = useRouter();
await updateImage();
router.refresh(); // Client-side refresh

Purpose: Keep UI in sync with data changes Types:

revalidatePath(): Server-side cache invalidation
router.refresh(): Client-side data refresh

Data Flow Diagrams

Creating a Project

┌────────────┐
│ Client │
└─────┬──────┘
│
│ 1. Submit form
│
┌─────▼──────────────┐
│ Server Action │
│ (with RLS) │
└─────┬──────────────┘
│
│ 2. Validate schema
│
┌─────▼──────────────┐
│ Service Layer │
└─────┬──────────────┘
│
│ 3. Insert with RLS
│
┌─────▼──────────────┐
│ Database │
│ (PostgreSQL+RLS) │
└─────┬──────────────┘
│
│ 4. Return data
│
┌─────▼──────────────┐
│ Revalidate Cache │
└─────┬──────────────┘
│
│ 5. Redirect
│
┌─────▼──────────────┐
│ New Page │
└────────────────────┘

Accessing Protected Data

┌─────────────┐
│ User Request│
└──────┬──────┘
│
│ 1. Request /home/team/projects/123
│
┌──────▼────────────┐
│ Server Component │
└──────┬────────────┘
│
│ 2. Get Supabase client (with user session)
│
┌──────▼────────────┐
│ Loader Function │
└──────┬────────────┘
│
│ 3. Query database
│
┌──────▼────────────┐
│ RLS Check │
│ (automatic) │
└──────┬────────────┘
│
├─ Authorized ──────┐
│ │
└─ Unauthorized ────┼─> Return empty/error
│
┌──────▼──────┐
│ Return Data │
└──────┬──────┘
│
┌──────▼──────┐
│ Render Page │
└─────────────┘

Performance Considerations

1. Indexes

All foreign keys are indexed:

CREATE INDEX idx_projects_account ON projects(account_id);
CREATE INDEX idx_memberships_user ON accounts_memberships(user_id);

2. Composite Indexes

For common queries:

CREATE INDEX idx_projects_slug ON projects(account_id, slug);

3. RLS Performance

RLS policies are optimized with indexes:

-- Fast membership checks
WHERE user_id = auth.uid() -- Indexed on user_id

4. N+1 Query Prevention

Use parallel data fetching:

// Good: Parallel
const [projects, members] = await Promise.all([
loadProjects(),
loadMembers(),
]);

// Bad: Sequential
const projects = await loadProjects();
const members = await loadMembers();

Security Best Practices

1. Always Use RLS

Every table has RLS policies:

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

2. Server-Only Code

Mark sensitive code:

import 'server-only';

3. Validate on Server

Never trust client:

export const updateProject = enhanceAction(
async (data) => {
// Validate with Zod
// RLS enforces permissions
},
{ schema: ProjectSchema }
);

4. SECURITY DEFINER Functions

Use carefully:

CREATE FUNCTION ... SECURITY DEFINER
SET search_path = ''; -- Prevent injection

Migration Strategy

Adding a New Feature

Database Migration

-- Add new table
CREATE TABLE feature_data (...);

-- Add RLS
ALTER TABLE feature_data ENABLE ROW LEVEL SECURITY;
CREATE POLICY ...

-- Add indexes
CREATE INDEX ...

Type Generation

pnpm supabase:web:typegen

Service Layer

export class FeatureService {
// Business logic
}

Server Actions

'use server';
export const featureAction = enhanceAction(...);

Components

// Server Component + Client Component

Architecture & Database Schema

Complete database schema, relationships, and architecture patterns for the multi-tenant SaaS platform

This document provides a comprehensive overview of the database schema, relationships, and architectural patterns used in the platform.

Database Schema Overview

Core Tables

1. auth.users (Supabase Auth)

CREATE TABLE auth.users (
id uuid PRIMARY KEY,
email varchar(255) UNIQUE,
encrypted_password varchar(255),
email_confirmed_at timestamptz,
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
);

Purpose: User authentication and identity Managed by: Supabase Auth Relationships: One-to-many with accounts_memberships

2. accounts

CREATE TABLE public.accounts (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
name varchar(150) NOT NULL,
slug varchar(150) UNIQUE NOT NULL,
description varchar(280),
picture_url varchar(1000),
status varchar(20) CHECK (status IN ('active', 'inactive')),
primary_owner_user_id uuid NOT NULL REFERENCES auth.users(id),
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
);

CREATE INDEX idx_accounts_slug ON accounts(slug);
CREATE INDEX idx_accounts_primary_owner ON accounts(primary_owner_user_id);

Purpose: Team accounts (multi-tenant workspaces) Type: Both personal and team accounts Key Fields:

slug: URL-friendly identifier (globally unique)
status: Account status (active/inactive)
primary_owner_user_id: Account creator/owner

3. accounts_memberships

CREATE TABLE public.accounts_memberships (
id bigserial PRIMARY KEY,
user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
role varchar(50) NOT NULL,
created_at timestamptz DEFAULT now(),
UNIQUE(user_id, account_id)
);

CREATE INDEX idx_memberships_user ON accounts_memberships(user_id);
CREATE INDEX idx_memberships_account ON accounts_memberships(account_id);

Purpose: User membership in accounts Roles: owner, manager, member, readonly Constraints: One user can have one role per account (UNIQUE constraint)

4. projects

CREATE TABLE public.projects (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
name varchar(255) NOT NULL,
slug varchar(128) NOT NULL,
description text,
picture_url varchar(1000),
active boolean DEFAULT true,
primary_owner_user_id uuid NOT NULL REFERENCES auth.users(id),
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now(),
UNIQUE(account_id, slug)
);

CREATE INDEX idx_projects_account ON projects(account_id);
CREATE INDEX idx_projects_slug ON projects(account_id, slug);

Purpose: Projects within team accounts Key Fields:

slug: URL-friendly identifier (unique per account)
active: Project status
account_id: Parent team account

5. projects_members

CREATE TABLE public.projects_members (
id bigserial PRIMARY KEY,
user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
role varchar(50) NOT NULL,
created_at timestamptz DEFAULT now(),
UNIQUE(user_id, project_id)
);

CREATE INDEX idx_project_members_user ON projects_members(user_id);
CREATE INDEX idx_project_members_project ON projects_members(project_id);

Entity Relationships

┌──────────────┐
│ auth.users │
└──────┬───────┘
│
│ 1:N
│
├─────────────────────────────────────┐
│ │
│ │
┌──────▼─────────────┐ ┌───────▼──────────┐
│ accounts (primary) │ │ accounts_members │
│ - owner │ │ - member │
└──────┬─────────────┘ └───────┬──────────┘
│ │
│ 1:N │
│ │
┌──────▼───────┐ ┌──────▼────────┐
│ projects │ │ projects │
│ │ │ (as member) │
└──────┬───────┘ └───────────────┘
│
│ 1:N
│
┌──────▼──────────┐
│ projects_members│
└─────────────────┘

Row Level Security (RLS)

Accounts RLS

-- Users can only see accounts they are members of
CREATE POLICY "Users can view their accounts"
ON accounts FOR SELECT
USING (
id IN (
SELECT account_id FROM accounts_memberships
WHERE user_id = auth.uid()
)
);

-- Only owners and managers can update
CREATE POLICY "Owners and managers can update accounts"
ON accounts FOR UPDATE
USING (
primary_owner_user_id = auth.uid()
OR
id IN (
SELECT account_id FROM accounts_memberships
WHERE user_id = auth.uid()
AND role IN ('owner', 'manager')
)
);

Projects RLS

-- Users can view projects if they're account or project members
CREATE POLICY "Users can view accessible projects"
ON projects FOR SELECT
USING (
-- Account member
account_id IN (
SELECT account_id FROM accounts_memberships
WHERE user_id = auth.uid()
)
OR
-- Project member
id IN (
SELECT project_id FROM projects_members
WHERE user_id = auth.uid()
)
);

-- Only project owners and managers can update
CREATE POLICY "Project owners and managers can update"
ON projects FOR UPDATE
USING (
id IN (
SELECT project_id FROM projects_members
WHERE user_id = auth.uid()
AND role IN ('owner', 'manager')
)
);

Database Functions

1. is_project_slug_unique

CREATE OR REPLACE FUNCTION is_project_slug_unique(
p_account_id uuid,
p_slug varchar,
p_project_id uuid DEFAULT NULL
)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
BEGIN
RETURN NOT EXISTS (
SELECT 1
FROM public.projects
WHERE account_id = p_account_id
AND slug = p_slug
AND (p_project_id IS NULL OR id != p_project_id)
);
END;
$$;

GRANT EXECUTE ON FUNCTION is_project_slug_unique TO authenticated;

Purpose: Validate project slug uniqueness within account Parameters:

p_account_id: Account to check within
p_slug: Slug to validate
p_project_id: Current project ID (for updates)

2. is_account_slug_unique

CREATE OR REPLACE FUNCTION is_account_slug_unique(
p_slug varchar,
p_account_id uuid DEFAULT NULL
)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
BEGIN
RETURN NOT EXISTS (
SELECT 1
FROM public.accounts
WHERE slug = p_slug
AND (p_account_id IS NULL OR id != p_account_id)
);
END;
$$;

GRANT EXECUTE ON FUNCTION is_account_slug_unique TO authenticated;

Purpose: Validate account slug uniqueness globally Parameters:

p_slug: Slug to validate
p_account_id: Current account ID (for updates)

Architecture Patterns

1. Server Components + Server Actions

// Page (Server Component)
async function ProjectSettingsPage({ params }) {
const { id } = await params;
const project = await loadProjectData(id);

return
<ProjectSettings project={project} />;
}

// Server Action
'use server';
export const updateProject = enhanceAction(
async (data) => {
const client = getSupabaseServerClient();
// RLS automatically enforces permissions
await client.from('projects').update(data).eq('id', data.id);
},
{ schema: ProjectSchema }
);

Benefits:

Automatic RLS enforcement
No API routes needed
Type-safe with Zod schemas
Automatic error handling

2. Loader Pattern

// Loader function (server-only)
import 'server-only';

export async function loadProjectSettingsPageData(
client: SupabaseClient,
projectId: string
) {
return Promise.all([
loadProject(client, projectId),
loadPermissions(client, projectId),
]);
}

// Used in page
const [project, permissions] = await loadProjectSettingsPageData(client, id);

Benefits:

Parallel data fetching
Server-only execution
Centralized data loading
Easy testing

3. Service Pattern

// Service layer
export class ProjectsService {
constructor(private client: SupabaseClient) {}

async createProject(data: CreateProjectData) {
const { data: project, error } = await this.client
.from('projects')
.insert(data)
.select()
.single();

if (error) return { error };
return { data: project };
}
}

// Usage in server action
const service = new ProjectsService(client);
const result = await service.createProject(data);

Benefits:

Reusable business logic
Testable
Encapsulation
Clear separation of concerns

4. Cache Revalidation

// Server action with revalidation
export const updateTeamAccount = enhanceAction(
async (params) => {
await updateAccount(params);

// Revalidate layout cache
revalidatePath(`/home/${params.slug}`, 'layout');

// Redirect if slug changed
if (slugChanged) {
redirect(`/home/${newSlug}/settings`);
}
}
);

// Client component
const router = useRouter();
await updateImage();
router.refresh(); // Client-side refresh

Purpose: Keep UI in sync with data changes Types:

revalidatePath(): Server-side cache invalidation
router.refresh(): Client-side data refresh

Data Flow Diagrams

Creating a Project

┌────────────┐
│ Client │
└─────┬──────┘
│
│ 1. Submit form
│
┌─────▼──────────────┐
│ Server Action │
│ (with RLS) │
└─────┬──────────────┘
│
│ 2. Validate schema
│
┌─────▼──────────────┐
│ Service Layer │
└─────┬──────────────┘
│
│ 3. Insert with RLS
│
┌─────▼──────────────┐
│ Database │
│ (PostgreSQL+RLS) │
└─────┬──────────────┘
│
│ 4. Return data
│
┌─────▼──────────────┐
│ Revalidate Cache │
└─────┬──────────────┘
│
│ 5. Redirect
│
┌─────▼──────────────┐
│ New Page │
└────────────────────┘

Accessing Protected Data

┌─────────────┐
│ User Request│
└──────┬──────┘
│
│ 1. Request /home/team/projects/123
│
┌──────▼────────────┐
│ Server Component │
└──────┬────────────┘
│
│ 2. Get Supabase client (with user session)
│
┌──────▼────────────┐
│ Loader Function │
└──────┬────────────┘
│
│ 3. Query database
│
┌──────▼────────────┐
│ RLS Check │
│ (automatic) │
└──────┬────────────┘
│
├─ Authorized ──────┐
│ │
└─ Unauthorized ────┼─> Return empty/error
│
┌──────▼──────┐
│ Return Data │
└──────┬──────┘
│
┌──────▼──────┐
│ Render Page │
└─────────────┘

Performance Considerations

1. Indexes

All foreign keys are indexed:

CREATE INDEX idx_projects_account ON projects(account_id);
CREATE INDEX idx_memberships_user ON accounts_memberships(user_id);

2. Composite Indexes

For common queries:

CREATE INDEX idx_projects_slug ON projects(account_id, slug);

3. RLS Performance

RLS policies are optimized with indexes:

-- Fast membership checks
WHERE user_id = auth.uid() -- Indexed on user_id

4. N+1 Query Prevention

Use parallel data fetching:

// Good: Parallel
const [projects, members] = await Promise.all([
loadProjects(),
loadMembers(),
]);

// Bad: Sequential
const projects = await loadProjects();
const members = await loadMembers();

Security Best Practices

1. Always Use RLS

Every table has RLS policies:

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

2. Server-Only Code

Mark sensitive code:

import 'server-only';

3. Validate on Server

Never trust client:

export const updateProject = enhanceAction(
async (data) => {
// Validate with Zod
// RLS enforces permissions
},
{ schema: ProjectSchema }
);

4. SECURITY DEFINER Functions

Use carefully:

CREATE FUNCTION ... SECURITY DEFINER
SET search_path = ''; -- Prevent injection

Migration Strategy

Adding a New Feature

Database Migration

-- Add new table
CREATE TABLE feature_data (...);

-- Add RLS
ALTER TABLE feature_data ENABLE ROW LEVEL SECURITY;
CREATE POLICY ...

-- Add indexes
CREATE INDEX ...

Type Generation

pnpm supabase:web:typegen

Service Layer

export class FeatureService {
// Business logic
}

Server Actions

'use server';
export const featureAction = enhanceAction(...);

Components

// Server Component + Client Component

Architecture & Database Schema

Complete database schema, relationships, and architecture patterns for the multi-tenant SaaS platform

Database Schema Overview

Core Tables

1. auth.users (Supabase Auth)

2. accounts

3. accounts_memberships

4. projects

5. projects_members

Entity Relationships

Row Level Security (RLS)

Accounts RLS

Projects RLS

Database Functions

1. is_project_slug_unique

2. is_account_slug_unique

Architecture Patterns

1. Server Components + Server Actions

2. Loader Pattern

3. Service Pattern

4. Cache Revalidation

Data Flow Diagrams

Creating a Project

Accessing Protected Data

Performance Considerations

1. Indexes

2. Composite Indexes

3. RLS Performance

4. N+1 Query Prevention

Security Best Practices

1. Always Use RLS

2. Server-Only Code

3. Validate on Server

4. SECURITY DEFINER Functions

Migration Strategy

Adding a New Feature

Related Documentation

Architecture & Database Schema

Complete database schema, relationships, and architecture patterns for the multi-tenant SaaS platform

Database Schema Overview

Core Tables

1. auth.users (Supabase Auth)

2. accounts

3. accounts_memberships

4. projects

5. projects_members

Entity Relationships

Row Level Security (RLS)

Accounts RLS

Projects RLS

Database Functions

1. is_project_slug_unique

2. is_account_slug_unique

Architecture Patterns

1. Server Components + Server Actions

2. Loader Pattern

3. Service Pattern

4. Cache Revalidation

Data Flow Diagrams

Creating a Project

Accessing Protected Data

Performance Considerations

1. Indexes

2. Composite Indexes

3. RLS Performance

4. N+1 Query Prevention

Security Best Practices

1. Always Use RLS

2. Server-Only Code

3. Validate on Server

4. SECURITY DEFINER Functions

Migration Strategy

Adding a New Feature

Related Documentation