ORGANIZATION MODEL

Organization Data Model

Overview

Organizations allow multiple users to collaborate under a shared workspace with centralized settings, branding, and data management.

Database Schema

organizations table

CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  logo_url TEXT,
  brand_colors JSONB DEFAULT '{"primary": "#000000", "secondary": "#ffffff"}',
  settings JSONB DEFAULT '{}',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

organization_members table

CREATE TABLE organization_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
  invited_by UUID REFERENCES auth.users(id),
  invited_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  joined_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(organization_id, user_id)
);

organization_invitations table

CREATE TABLE organization_invitations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  role TEXT NOT NULL CHECK (role IN ('admin', 'member')),
  invited_by UUID NOT NULL REFERENCES auth.users(id),
  token TEXT UNIQUE NOT NULL,
  expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
  accepted_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(organization_id, email)
);

Roles & Permissions

Owner

• Full access to all organization settings
• Can delete the organization
• Can transfer ownership
• Can manage all members

Admin

• Can manage organization settings (branding, templates, etc.)
• Can invite/remove members (except owners)
• Can view all organization data

Member

• Can view organization resources
• Cannot modify organization settings
• Can use organization templates and branding

Migration from Current System

Current Issues

• Enterprise settings (enterprise_settings table) are per-user, not per-organization
• Branding settings are in global user settings instead of org-level admin
• No concept of shared resources across team members

Migration Steps

1. Create new organization tables
2. Migrate existing enterprise_settings to organizations
3. Create default organization for each existing user (if they have enterprise settings)
4. Move branding/templates tabs from settings to admin pages
5. Update storage structure: workspaces/{user_id}/ → organizations/{org_id}/

Frontend Structure

/admin/
├── organization/     # Organization settings (name, slug, members)
├── branding/        # Logo, colors (moved from settings)
├── templates/       # Agent/prompt templates (moved from settings)
├── data/           # Data management (moved from settings)
└── members/        # Member management & invitations

API Endpoints

GET    /api/organizations                 # List user's organizations
POST   /api/organizations                 # Create new organization
GET    /api/organizations/:id             # Get organization details
PATCH  /api/organizations/:id             # Update organization
DELETE /api/organizations/:id             # Delete organization

GET    /api/organizations/:id/members     # List members
POST   /api/organizations/:id/members     # Invite member
DELETE /api/organizations/:id/members/:id # Remove member

POST   /api/organizations/:id/invitations # Create invitation
GET    /api/invitations/:token            # Get invitation details
POST   /api/invitations/:token/accept     # Accept invitation

Usage Flow

1. New User: Automatically creates a personal organization on first login
2. Create Organization: User creates a new organization (becomes owner)
3. Invite Members: Owner/admin sends invitation emails
4. Accept Invitation: User clicks email link, joins organization
5. Switch Organization: User can switch between organizations they belong to
6. Admin Settings: Only owners/admins can access /admin/* pages