┌─────────────────────────────────────────────────────────────────────────┐
│ USER SIGNS UP │
├─────────────────────────────────────────────────────────────────────────┤
│ 1. User clicks "Sign Up" → Clerk handles (email, Google, GitHub) │
│ 2. On first login → Workspace auto-created │
│ 3. User lands in their personal workspace │
│ 4. User can invite team members (becomes team workspace) │
│ 5. Clerk Billing handles plan upgrades │
└─────────────────────────────────────────────────────────────────────────┘
┌──────────────────┐ ┌──────────────────┐
│ workspaces │ │ users │
├──────────────────┤ ├──────────────────┤
│ id (UUID) PK │ │ id SERIAL PK │
│ name │ │ clerk_user_id │◄── From Clerk
│ slug │ │ email │
│ owner_id FK │──────►│ name │
│ clerk_org_id │ │ avatar_url │
│ plan │ │ last_sign_in │
│ plan_limits │ │ created_at │
│ settings JSONB │ └──────────────────┘
│ created_at │ │
└──────────────────┘ │
│ │
▼ ▼
┌──────────────────────────────────────────────┐
│ workspace_members │
├──────────────────────────────────────────────┤
│ workspace_id UUID FK │
│ user_id INTEGER FK │
│ role (owner | member) │
│ joined_at │
└──────────────────────────────────────────────┘
-- ================================================================
-- SAAS FOUNDATION TABLES (PRD-37 v2.0)
-- ================================================================
-- Workspaces (simplified from tenants)
CREATE TABLE IF NOT EXISTS workspaces (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
name VARCHAR(255) NOT NULL,
slug VARCHAR(100) UNIQUE,
owner_id INTEGER NOT NULL REFERENCES users(id),
clerk_org_id VARCHAR(255) UNIQUE,
plan VARCHAR(50) DEFAULT 'starter' CHECK (plan IN ('starter', 'business', 'enterprise')),
plan_limits JSONB DEFAULT '{
"max_agents": 3,
"max_workflows": 10,
"max_documents": 5,
"max_members": 1
}'::jsonb,
settings JSONB DEFAULT '{}'::jsonb,
is_personal BOOLEAN DEFAULT TRUE,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_workspaces_owner ON workspaces(owner_id);
CREATE INDEX idx_workspaces_clerk_org ON workspaces(clerk_org_id);
CREATE INDEX idx_workspaces_slug ON workspaces(slug);
CREATE INDEX idx_workspaces_plan ON workspaces(plan);
-- Workspace Members
CREATE TABLE IF NOT EXISTS workspace_members (
id SERIAL PRIMARY KEY,
workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role VARCHAR(50) NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'member')),
invited_by INTEGER REFERENCES users(id),
invited_at TIMESTAMP,
joined_at TIMESTAMP DEFAULT NOW(),
is_active BOOLEAN DEFAULT TRUE,
UNIQUE(workspace_id, user_id)
);
CREATE INDEX idx_members_workspace ON workspace_members(workspace_id);
CREATE INDEX idx_members_user ON workspace_members(user_id);
-- Extend Users table for Clerk
ALTER TABLE users ADD COLUMN IF NOT EXISTS clerk_user_id VARCHAR(255) UNIQUE;
ALTER TABLE users ADD COLUMN IF NOT EXISTS name VARCHAR(255);
ALTER TABLE users ADD COLUMN IF NOT EXISTS avatar_url VARCHAR(500);
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_sign_in TIMESTAMP;
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT TRUE;
CREATE INDEX IF NOT EXISTS idx_users_clerk ON users(clerk_user_id);
-- API Keys (for widget integration)
CREATE TABLE IF NOT EXISTS api_keys (
id SERIAL PRIMARY KEY,
workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
name VARCHAR(255) NOT NULL,
key_hash VARCHAR(255) NOT NULL,
key_prefix VARCHAR(12) NOT NULL,
scopes JSONB DEFAULT '["read", "write"]'::jsonb,
last_used_at TIMESTAMP,
expires_at TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_api_keys_workspace ON api_keys(workspace_id);
CREATE INDEX idx_api_keys_prefix ON api_keys(key_prefix);
CREATE INDEX idx_api_keys_hash ON api_keys(key_hash);
-- Usage Tracking (tokens, tool calls)
CREATE TABLE IF NOT EXISTS usage_logs (
id SERIAL PRIMARY KEY,
workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
user_id INTEGER REFERENCES users(id),
metric_type VARCHAR(50) NOT NULL CHECK (metric_type IN (
'llm_tokens_input', 'llm_tokens_output', 'tool_call',
'agent_run', 'workflow_run', 'document_upload'
)),
quantity INTEGER NOT NULL DEFAULT 1,
model VARCHAR(100),
metadata JSONB DEFAULT '{}'::jsonb,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_usage_workspace ON usage_logs(workspace_id);
CREATE INDEX idx_usage_type ON usage_logs(metric_type);
CREATE INDEX idx_usage_created ON usage_logs(created_at DESC);
-- Usage Summary (daily rollups for dashboard)
CREATE TABLE IF NOT EXISTS usage_summary (
id SERIAL PRIMARY KEY,
workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
date DATE NOT NULL,
metric_type VARCHAR(50) NOT NULL,
total_quantity INTEGER NOT NULL DEFAULT 0,
UNIQUE(workspace_id, date, metric_type)
);
CREATE INDEX idx_usage_summary_workspace ON usage_summary(workspace_id, date);
-- Add workspace_id to existing tables
ALTER TABLE agents ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES workspaces(id);
ALTER TABLE workflows ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES workspaces(id);
ALTER TABLE documents ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES workspaces(id);
ALTER TABLE chats ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES workspaces(id);
ALTER TABLE credentials ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES workspaces(id);
ALTER TABLE mcp_tools ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES workspaces(id);
ALTER TABLE skills ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES workspaces(id);
CREATE INDEX IF NOT EXISTS idx_agents_workspace ON agents(workspace_id);
CREATE INDEX IF NOT EXISTS idx_workflows_workspace ON workflows(workspace_id);
CREATE INDEX IF NOT EXISTS idx_documents_workspace ON documents(workspace_id);
CREATE INDEX IF NOT EXISTS idx_chats_workspace ON chats(workspace_id);
CREATE INDEX IF NOT EXISTS idx_credentials_workspace ON credentials(workspace_id);
-- Migration 037: SaaS Foundation (Workspaces)
-- Simplified multi-workspace support with Clerk auth
BEGIN;
-- [Include all CREATE TABLE and ALTER TABLE from above]
-- Insert default system workspace (for migration of existing data)
INSERT INTO workspaces (id, name, slug, owner_id, plan, is_personal, is_active)
SELECT
'00000000-0000-0000-0000-000000000000',
'System Default',
'system',
1, -- Assumes user ID 1 exists
'enterprise',
FALSE,
TRUE
WHERE EXISTS (SELECT 1 FROM users WHERE id = 1)
ON CONFLICT DO NOTHING;
-- Migrate existing data to system workspace
UPDATE agents SET workspace_id = '00000000-0000-0000-0000-000000000000' WHERE workspace_id IS NULL;
UPDATE workflows SET workspace_id = '00000000-0000-0000-0000-000000000000' WHERE workspace_id IS NULL;
UPDATE documents SET workspace_id = '00000000-0000-0000-0000-000000000000' WHERE workspace_id IS NULL;
UPDATE chats SET workspace_id = '00000000-0000-0000-0000-000000000000' WHERE workspace_id IS NULL;
UPDATE credentials SET workspace_id = '00000000-0000-0000-0000-000000000000' WHERE workspace_id IS NULL;
COMMIT;
# PRD-37: SaaS Foundation
# Clerk JWT Verification
PyJWT>=2.8.0
cryptography>=41.0.0
# API Key Generation (stdlib, no install needed)
# secrets, hashlib
# Rate Limiting
slowapi>=0.1.9
# core/auth/clerk.py
from typing import Optional, Dict, Any
import jwt
from jwt import PyJWKClient
import os
class ClerkAuth:
"""Clerk JWT verification."""
def __init__(self):
self.jwks_url = os.getenv("CLERK_JWKS_URL")
# Extract issuer/audience from JWKS URL (e.g., https://app.clerk.accounts.dev)
# Note: Clerk session tokens do not include an "aud" claim by default.
# Only set audience if explicitly configured in Clerk or via CLERK_AUDIENCE env var.
self.audience = os.getenv("CLERK_AUDIENCE")
if not self.audience and self.jwks_url:
# Derive from JWKS URL: https://app.clerk.accounts.dev/.well-known/jwks.json -> https://app.clerk.accounts.dev
# This is only used if Clerk tokens are configured with aud claim
self.audience = self.jwks_url.replace("/.well-known/jwks.json", "")
self._jwks_client = None
@property
def jwks_client(self) -> PyJWKClient:
if self._jwks_client is None:
self._jwks_client = PyJWKClient(self.jwks_url)
return self._jwks_client
def verify_token(self, token: str) -> Optional[Dict[str, Any]]:
"""Verify Clerk JWT and return claims."""
try:
signing_key = self.jwks_client.get_signing_key_from_jwt(token)
# Clerk session tokens do not include "aud" claim by default.
# Only verify audience if explicitly configured (self.audience is set).
# If audience is not set, disable audience verification to avoid failures.
decode_options = {"verify_exp": True}
if self.audience:
decode_options["verify_aud"] = True
return jwt.decode(
token,
signing_key.key,
algorithms=["RS256"],
audience=self.audience,
options=decode_options
)
else:
# No audience configured - skip audience verification
decode_options["verify_aud"] = False
return jwt.decode(
token,
signing_key.key,
algorithms=["RS256"],
options=decode_options
)
except Exception:
return None
# core/auth/dependencies.py
from fastapi import Depends, HTTPException, Request
from fastapi.security import HTTPBearer
from dataclasses import dataclass
from uuid import UUID
@dataclass
class RequestContext:
user_id: int
clerk_user_id: str
email: str
workspace_id: UUID
workspace_name: str
role: str # owner | member
plan: str # starter | business | enterprise
async def get_context(
request: Request,
credentials = Depends(HTTPBearer())
) -> RequestContext:
"""Get authenticated request context with workspace."""
# Verify JWT
claims = clerk_auth.verify_token(credentials.credentials)
if not claims:
raise HTTPException(401, "Invalid token")
# Get/create user
user = get_or_create_user(claims)
# Get workspace (from header or default)
workspace_id = request.headers.get("X-Workspace-ID")
workspace = get_user_workspace(user.id, workspace_id)
if not workspace:
raise HTTPException(404, "Workspace not found")
membership = get_membership(workspace.id, user.id)
return RequestContext(
user_id=user.id,
clerk_user_id=user.clerk_user_id,
email=user.email,
workspace_id=workspace.id,
workspace_name=workspace.name,
role=membership.role,
plan=workspace.plan,
)
# core/workspaces/service.py
class WorkspaceService:
"""Workspace management."""
def create_personal_workspace(self, user_id: int, email: str) -> Workspace:
"""Create personal workspace on first login."""
slug = email.split("@")[0].lower()
return Workspace(
name=f"{slug}'s Workspace",
slug=self._unique_slug(slug),
owner_id=user_id,
plan="starter",
is_personal=True,
)
def invite_member(self, workspace_id: UUID, email: str, inviter_id: int):
"""Invite a team member."""
# Check plan limits
if not self._can_add_member(workspace_id):
raise LimitExceeded("Member limit reached. Upgrade plan.")
# Send invite via Clerk
pass
def check_limit(self, workspace_id: UUID, resource: str) -> bool:
"""Check if workspace has capacity for resource."""
workspace = self.get(workspace_id)
limits = workspace.plan_limits
current = self._count_resource(workspace_id, resource)
max_allowed = limits.get(f"max_{resource}", 0)
return max_allowed == -1 or current < max_allowed
# core/usage/service.py
class UsageService:
"""Track usage for analytics and limits."""
def log(
self,
workspace_id: UUID,
metric_type: str,
quantity: int = 1,
user_id: int = None,
model: str = None,
metadata: dict = None
):
"""Log a usage event."""
self.db.add(UsageLog(
workspace_id=workspace_id,
user_id=user_id,
metric_type=metric_type,
quantity=quantity,
model=model,
metadata=metadata or {},
))
self.db.commit()
def get_summary(self, workspace_id: UUID, days: int = 30) -> dict:
"""Get usage summary for dashboard."""
# Aggregate from usage_logs or usage_summary
pass
