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 statusaccount_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 withinp_slug: Slug to validatep_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 validatep_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 invalidationrouter.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