← reference

Organizations, Teams, and Roles System

Overview

The system supports a hierarchical structure:

  • Organizations (top level)
  • Teams (within organizations)
  • Users (within teams and/or organizations)

Roles

Global Roles (stored in profiles.role)

  • super_admin - Full system access, can manage everything
  • user - Regular user (default)

Organization Roles (stored in user_organizations.role)

  • owner - Owns the organization (also stored in organizations.owner_id)
  • admin - Organization administrator
  • member - Regular organization member

Team Roles (stored in user_teams.role)

  • admin - Team administrator (also stored in teams.admin_id)
  • member - Regular team member

Hierarchy and Permissions

Permission Inheritance

  1. Super Admins have access to everything
  2. Org Owners have full access to their organization and all teams within it
  3. Team Admins have full access to their team
  4. Org Members can view and participate in org activities
  5. Team Members can view and participate in team activities

Access Rules

  • Super admins can access all orgs and teams
  • Org owners can access all teams in their org
  • Team admins can manage their team
  • Users can only access orgs/teams they're members of

Database Schema

Tables

organizations

  • id (UUID, primary key)
  • name (TEXT, required)
  • description (TEXT)
  • owner_id (UUID, references auth.users)
  • settings (JSONB)
  • created_at, updated_at

teams

  • id (UUID, primary key)
  • org_id (UUID, references organizations, required)
  • name (TEXT, required)
  • description (TEXT)
  • admin_id (UUID, references auth.users)
  • settings (JSONB)
  • created_at, updated_at

user_organizations

  • id (UUID, primary key)
  • user_id (UUID, references auth.users)
  • org_id (UUID, references organizations)
  • role (TEXT: 'owner', 'admin', 'member')
  • created_at, updated_at
  • Unique constraint on (user_id, org_id)

user_teams

  • id (UUID, primary key)
  • user_id (UUID, references auth.users)
  • team_id (UUID, references teams)
  • role (TEXT: 'admin', 'member')
  • created_at, updated_at
  • Unique constraint on (user_id, team_id)

profiles (updated)

  • Added role column (TEXT: 'super_admin', 'user')

Helper Functions

Database Functions (PostgreSQL)

  • is_super_admin(user_id) - Check if user is super admin
  • is_org_owner(user_id, org_id) - Check if user owns an org
  • is_org_member(user_id, org_id) - Check if user is member of org
  • is_team_admin(user_id, team_id) - Check if user is team admin
  • is_team_member(user_id, team_id) - Check if user is team member

TypeScript Helpers (apps/backend/src/database/roles.ts)

  • isSuperAdmin(supabase, userId)
  • isOrgOwner(supabase, userId, orgId)
  • isOrgMember(supabase, userId, orgId)
  • isTeamAdmin(supabase, userId, teamId)
  • isTeamMember(supabase, userId, teamId)
  • getUserOrgRole(supabase, userId, orgId)
  • getUserTeamRole(supabase, userId, teamId)
  • getUserOrganizations(supabase, userId)
  • getUserTeams(supabase, userId)

Row Level Security (RLS)

All tables have RLS enabled with policies that:

  • Allow super admins full access
  • Allow org owners to manage their orgs
  • Allow team admins to manage their teams
  • Allow members to view their orgs/teams
  • Prevent unauthorized access

Setting Up Super Admin

The super admin role is automatically set for sandonjurowski@gmail.com in:

  1. Migration 003_add_orgs_teams_roles.sql (if user exists)
  2. Firebase importer script (when creating/updating user)

Usage Examples

Create an Organization

INSERT INTO organizations (name, description, owner_id)
VALUES ('Acme Corp', 'A great company', 'user-uuid-here');

Add User to Organization

INSERT INTO user_organizations (user_id, org_id, role)
VALUES ('user-uuid', 'org-uuid', 'member');

Create a Team

INSERT INTO teams (org_id, name, admin_id)
VALUES ('org-uuid', 'Engineering', 'user-uuid');

Add User to Team

INSERT INTO user_teams (user_id, team_id, role)
VALUES ('user-uuid', 'team-uuid', 'member');

Check Permissions (TypeScript)

import { isSuperAdmin, isOrgOwner, isTeamAdmin } from '@/database/roles'

// Check if user is super admin
const isAdmin = await isSuperAdmin(supabase, userId)

// Check if user owns an org
const ownsOrg = await isOrgOwner(supabase, userId, orgId)

// Check if user is team admin
const isAdmin = await isTeamAdmin(supabase, userId, teamId)

Notes

  • Users can belong to multiple organizations
  • Users can belong to multiple teams (even across different orgs)
  • The owner_id in organizations and admin_id in teams are the primary owners/admins
  • Additional roles are stored in user_organizations and user_teams for flexibility
  • Journal entries remain user-specific (not tied to orgs/teams) - this can be extended later if needed
ORG TEAM ROLES — Docs | HiveJournal