feat(backend): Add multi-tenancy with tenant_id scoping and default organization

[mjunaidca]

Problem Statement

Current State: Backend API (Taskflow API) has no organization/tenant scoping. All users see all projects/tasks regardless of which organization they're in via the org switcher.

Security Risk: 🔴 CRITICAL - Without tenant isolation, users can access each other's data.

UX Issue: Users switch organizations in the UI but see the same data (confusing and broken).

Proposed Solution

Add tenant_id to core models and filter all queries by the authenticated user's active tenant from JWT.

1. Database Schema Changes

Add tenant_id column to:

• ✅ project table
• ✅ task table
• ✅ worker table
• ✅ audit_log table

# Example: Project model
class Project(SQLModel, table=True):
    # ... existing fields ...
    tenant_id: str = Field(
        index=True,
        description="Organization ID from SSO (tenant isolation)"
    )

2. Default Organization Strategy

Problem: Users shouldn't need to understand organizations to use Taskflow.

Solution: Auto-create "Personal Workspace" as default organization.

When to Create Default Org:

1. First API request - If user has no tenant_id in JWT:

   • Call SSO API to create "Personal Workspace" org
   • SSO assigns user as owner
   • SSO updates session with new tenant_id
   • Redirect user to retry request (new JWT with tenant_id)

2. Alternative: During signup - SSO creates default org immediately

Benefits:

• ✅ Seamless single-user experience
• ✅ No need to explain organizations upfront
• ✅ Users can add orgs later as they grow
• ✅ Zero setup friction

3. Query Filtering

Add tenant scoping to ALL queries:

# Before (INSECURE)
projects = session.exec(select(Project)).all()

# After (SECURE)
tenant_id = get_tenant_from_jwt(request)
projects = session.exec(
    select(Project).where(Project.tenant_id == tenant_id)
).all()

Where to apply:

• All GET /projects, GET /tasks, GET /workers endpoints
• All POST, PUT, DELETE operations (verify tenant ownership)
• Audit log queries

4. Middleware for tenant_id Extraction

async def inject_tenant_id(request: Request, call_next):
    """Extract tenant_id from JWT and attach to request state."""
    jwt_claims = decode_jwt(request.cookies.get("taskflow_id_token"))
    tenant_id = jwt_claims.get("tenant_id")
    
    if not tenant_id:
        # User has no org - trigger default org creation
        return await create_default_org_flow(request)
    
    request.state.tenant_id = tenant_id
    return await call_next(request)

5. Migration Strategy

For existing data:

-- Option 1: Assign to user's first organization
UPDATE project p
SET tenant_id = (
    SELECT organization_id 
    FROM member 
    WHERE user_id = p.owner_id 
    LIMIT 1
);

-- Option 2: Create default org for each user, assign all their data
-- (Safer - preserves all data under user's control)

Implementation Checklist

Phase 1: Schema & Migration

• Add tenant_id column to Project, Task, Worker, AuditLog models
• Create migration script (Alembic)
• Backfill existing data with default org per user
• Add indexes on tenant_id columns

Phase 2: Default Organization

• SSO: Create default "Personal Workspace" org on user signup
• OR: Backend middleware creates default org on first API request
• Handle edge case: user deletes all orgs (recreate default)

Phase 3: Query Filtering

• Add tenant_id extraction middleware
• Update ALL query endpoints to filter by tenant_id
• Update ALL mutations to verify tenant ownership
• Add tenant_id to audit logs

Phase 4: Security Validation

• Test: User A cannot see User B's projects
• Test: User A cannot modify User B's tasks
• Test: Switching orgs shows different data
• Test: Default org created automatically

Phase 5: Documentation

• Update API docs with tenant scoping behavior
• Document default org creation flow
• Add migration guide for existing deployments

Impact Analysis

Security

• Before: 🔴 All users see all data (tenant_id ignored)
• After: ✅ Complete data isolation per organization

UX

• Before: Org switcher doesn't work (data unchanged)
• After: ✅ Switching orgs shows that org's data

Default Org Behavior

• Single user: ✅ Never sees organizations, just works
• Team user: ✅ Can switch between orgs seamlessly
• Growth path: ✅ Start alone, invite team later

Performance

• Query impact: Minimal (indexed tenant_id filter)
• Migration: One-time cost to backfill existing data

Naming: tenant_id vs organization_id?

Recommendation: tenant_id

Why:

• ✅ Matches JWT claim name (tenant_id)
• ✅ Standard multi-tenancy terminology
• ✅ Consistent with SSO (session has activeOrganizationId → JWT has tenant_id)

Note: ChatKit store already uses organization_id - consider aligning both to tenant_id for consistency.

Related

• Closes organization data isolation requirement
• Depends on: SSO organization switching (feat(organizations): Complete Organization Management UI #19)
• Spec: TBD (needs /sp.specify for multi-tenancy backend)

Questions for Discussion

1. Default org creation timing: Signup vs first API request?
2. Default org name: "Personal Workspace" or user's name?
3. Rename ChatKit organization_id to tenant_id for consistency?
4. Migration strategy: Assign to first org or create default per user?

---

🤖 Generated with Claude Code

[mjunaidca]

✅ Decisions Made

After discussion, here are the confirmed implementation decisions:

1. Default Organization Creation: During Signup at SSO ✅

When: Automatically during user registration at SSO platform
What: Create "Personal Workspace" organization
Why:

• Zero setup friction for new users
• Guarantees every user always has a tenant_id
• No special handling needed in backend for "no org" case
• Seamless single-user → team transition

Implementation Location: SSO platform signup flow

2. Default Organization Name: "Personal Workspace" ✅

Rationale:

• Generic and professional
• Works for both individuals and teams
• No personalization needed (keeps PII minimal)
• Consistent across all users
• Users can rename later if needed

Alternative considered: "{User's Name}'s Workspace" - rejected due to PII concerns and unnecessary complexity

3. ChatKit Alignment: Rename to tenant_id ✅

Change: ChatKit's organization_id → tenant_id
Why:

• Consistency across entire stack (JWT → Backend → DB)
• Standard multi-tenancy terminology
• Reduces cognitive load (one term, not two)
• Matches SSO's JWT claim name

---

Updated Implementation Plan

Phase 0: SSO Default Organization (DO THIS FIRST) 🔴

Critical Path: Must be completed before Phase 2

• Modify SSO signup flow to create "Personal Workspace" org
• Assign new user as owner with admin role
• Update session with activeOrganizationId
• Ensure JWT includes tenant_id on first login
• Handle edge case: User deletes all orgs → recreate default
• Migration: Create default org for existing users without orgs

Files to modify:

• sso-platform/src/lib/auth.ts (signup hooks)
• sso-platform/src/app/api/auth/[...all]/route.ts (registration flow)

Success Criteria:

• Every new user has exactly 1 org after signup
• tenant_id appears in JWT claims immediately
• No user can exist without at least one organization

---

Phase 1: Backend Schema Migration

Only start after Phase 0 is complete (ensures all users have tenant_id)

• Rename ChatKit organization_id to tenant_id for consistency
• Add tenant_id column to Project, Task, Worker, AuditLog models
• Create Alembic migration script
• Backfill: Assign existing data to user's default "Personal Workspace"
• Add indexes on tenant_id columns (critical for query performance)

Migration Strategy:

-- All users now have default org from Phase 0
UPDATE project p
SET tenant_id = (
    SELECT organization_id 
    FROM member 
    WHERE user_id = p.owner_id 
    AND organization_id IN (
        SELECT id FROM organization WHERE name = 'Personal Workspace'
    )
    LIMIT 1
);

---

Phase 2: Middleware & Query Filtering

• Create middleware to extract tenant_id from JWT
• Attach tenant_id to request state (request.state.tenant_id)
• Update dependency injection to provide tenant context
• Modify ALL query endpoints to filter by tenant_id
• Verify ALL mutations check tenant ownership before writes
• Add tenant_id to audit log entries

Key Middleware:

async def inject_tenant_id(request: Request, call_next):
    """Extract tenant_id from JWT and attach to request state."""
    jwt_claims = decode_jwt(request.cookies.get("taskflow_id_token"))
    tenant_id = jwt_claims.get("tenant_id")
    
    if not tenant_id:
        # This should NEVER happen after Phase 0
        raise HTTPException(
            status_code=401, 
            detail="No tenant_id in JWT. Please re-login."
        )
    
    request.state.tenant_id = tenant_id
    return await call_next(request)

---

Phase 3: Security Validation & Testing

• Test: User A cannot see User B's projects
• Test: User A cannot modify User B's tasks
• Test: Switching orgs shows different data
• Test: Default org exists for all users
• Test: Creating project auto-assigns current tenant_id
• Test: Deleting last org triggers default org recreation
• Load test: Query performance with tenant_id filtering

---

Phase 4: UX Polish

• Hide org switcher if user has only 1 org (Personal Workspace)
• Show org switcher only when user joins/creates 2nd org
• Document org switching behavior in UI
• Add "Invite to Organization" flow
• Update onboarding to mention teams (but don't force it)

---

User Experience Flow

Single User (Default Path)

1. Sign up → Auto-creates "Personal Workspace"
2. Create project → Assigned to "Personal Workspace" (invisible to user)
3. No org switcher shown (only 1 org)
4. User never thinks about organizations

Growing Team

1. User creates 2nd org: "My Startup"
2. Org switcher appears in nav
3. Switch between "Personal Workspace" ↔ "My Startup"
4. Each org has separate projects/tasks/members

Enterprise User

1. User belongs to 5 orgs
2. Org switcher shows all 5
3. Switch seamlessly (Slack/Notion pattern)
4. Complete data isolation between orgs

---

Success Metrics

✅ Security: Zero cross-tenant data leaks
✅ UX: Single users never see "organization"
✅ Performance: tenant_id queries remain fast (<50ms p95)
✅ Migration: All existing users get default org, zero data loss
✅ Growth: Seamless path from solo → team → enterprise

---

Next Steps

1. Immediate: Start Phase 0 (SSO default org creation)
2. After Phase 0: Begin backend schema migration
3. Testing: Comprehensive security validation
4. Documentation: Update API docs with tenant scoping

---

🤖 Updated with Claude Code

[mjunaidca]

Simplified Implementation Analysis

After review, multi-tenancy is much simpler than originally scoped:

Changes Required (API only - 4 files)

File	Change
models/project.py	Add tenant_id: str = Field(index=True)
schemas/project.py	Add tenant_id to create/read schemas
auth.py	Extract tenant_id from JWT OR use default "taskflow"
routers/projects.py	Add .where(Project.tenant_id == tenant_id) to all queries

Why Other Components Don't Need Changes

• MCP Server: Calls API via HTTP → gets filtered results → no code changes
• Frontend: Sends JWT to API → gets filtered results → no code changes
• Tasks/Workers: Already scoped by project_id → inherit tenant boundary

Simplified Strategy

Use a default tenant ("taskflow") for all users initially. This avoids SSO changes and org-switching complexity.

Estimated Time: 30-45 minutes

---

🤖 Analysis by Gemini