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

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:

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)
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.

Questions for Discussion

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

🤖 Generated with Claude Code

✅ 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

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

🤖 Updated with Claude Code

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

Merge add multi tenancy support in taskflow api and fix org switcher …

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

Description