Organization Multi-Tenancy Implementation

Overview

This document describes the implementation of organization-based multi-tenancy for the loan management system. This ensures complete data isolation between different companies/organizations using the platform.

🔒 Security Features

Data Isolation

• Organization-scoped queries: All database queries are filtered by organization_id
• Row Level Security (RLS): Database-level policies prevent cross-organization data access
• API-level protection: All API endpoints verify organization membership
• File storage isolation: Documents are organized by organization in secure storage

Authentication & Authorization

• Organization context: Users must belong to an active organization to access data
• Role-based access: Users have roles within organizations (owner, admin, member)
• Session validation: Every request validates organization membership

📊 Database Schema Changes

New Tables

organizations

- id (uuid, primary key)
- name (text, not null)
- slug (text, unique, not null) 
- domain (text, optional)
- settings (json)
- subscription_status (text, default: 'trial')
- subscription_plan (text, default: 'basic')
- created_at, updated_at (timestamps)

user_organizations (Many-to-Many)

- id (uuid, primary key)
- user_id (uuid, references users_table)
- organization_id (uuid, references organizations)
- role (text: 'owner', 'admin', 'member')
- status (text: 'active', 'inactive', 'pending')
- joined_at (timestamp)

Updated Tables

All existing tables now include:

• organization_id (uuid, not null, references organizations)
• created_by / uploaded_by fields for audit trails

Tables Updated:

• projects (loan files)
• draws (funding requests)
• documents (uploaded files)
• decisions (approval/rejection records)
• requirements (draw type requirements)
• ai_models (AI processing models)
• users_table (added default_organization_id)

🛡️ Row Level Security (RLS) Policies

Organization Access

-- Users can only see organizations they belong to
CREATE POLICY "Users can view their organizations" ON organizations
  FOR SELECT USING (
    id IN (
      SELECT organization_id FROM user_organizations 
      WHERE user_id = auth.uid() AND status = 'active'
    )
  );

Data Access Policies

All data tables have policies ensuring users can only access data from their organizations:

-- Example: Projects table
CREATE POLICY "Users can view organization projects" ON projects
  FOR SELECT USING (
    organization_id IN (
      SELECT organization_id FROM user_organizations 
      WHERE user_id = auth.uid() AND status = 'active'
    )
  );

Service Role Bypass

Service role can bypass all RLS policies for admin operations:

CREATE POLICY "Service role can do anything" ON [table]
  USING (auth.role() = 'service_role');

🔧 Implementation Details

Organization Context Utilities

utils/auth/organization.ts

Key functions for organization-aware operations:

// Get current user's organization context
getCurrentUserOrganization(): Promise<OrganizationContext | null>

// Require organization context (throws if not found)
requireOrganizationContext(): Promise<OrganizationContext>

// Switch user's active organization
switchOrganization(organizationId: string): Promise<boolean>

// Get user's organizations
getUserOrganizations(userId: string): Promise<SelectOrganization[]>

Organization Context Interface

interface OrganizationContext {
  user: SelectUser
  organization: SelectOrganization
  userRole: string
  userOrganizations: SelectOrganization[]
}

Updated Server Actions

All server actions now include organization context:

export async function createProject(formData: FormData) {
  // CRITICAL: Get organization context for data isolation
  const context = await requireOrganizationContext()
  
  const [project] = await db.insert(projectsTable).values({
    organizationId: context.organization.id, // CRITICAL: Organization isolation
    // ... other fields
  }).returning()
}

Database Query Patterns

Organization-Aware Queries

// ✅ CORRECT: Organization-scoped query
const draws = await db
  .select()
  .from(drawsTable)
  .where(eq(drawsTable.organizationId, context.organization.id))

// ❌ WRONG: Global query (security risk)
const draws = await db.select().from(drawsTable)

Cross-Table Joins with Organization Verification

const drawDetails = await db
  .select()
  .from(drawsTable)
  .innerJoin(projectsTable, eq(drawsTable.projectId, projectsTable.id))
  .where(
    and(
      eq(drawsTable.id, drawId),
      eq(drawsTable.organizationId, context.organization.id) // Verify organization
    )
  )

🚀 Migration Process

1. Schema Migration

• Added organization tables and relationships
• Added organization_id columns to existing tables
• Created foreign key constraints

2. Data Migration

• Created default organization for existing data
• Assigned all existing records to default organization
• Created user-organization relationships

3. RLS Policy Application

• Enabled RLS on all tables
• Created organization-scoped access policies
• Added service role bypass policies

4. Application Updates

• Updated all server actions with organization context
• Modified database queries to be organization-aware
• Updated API endpoints with organization filtering

📋 Usage Examples

Creating Organization-Aware Records

// Server action example
export async function createDraw(formData: FormData) {
  const context = await requireOrganizationContext()
  
  // Verify project belongs to organization
  const projectCheck = await db
    .select({ id: projectsTable.id })
    .from(projectsTable)
    .where(
      and(
        eq(projectsTable.id, projectId),
        eq(projectsTable.organizationId, context.organization.id)
      )
    )
  
  if (projectCheck.length === 0) {
    throw new Error('Project not found or access denied')
  }
  
  // Create draw with organization context
  const [draw] = await db.insert(drawsTable).values({
    organizationId: context.organization.id,
    projectId,
    // ... other fields
  }).returning()
}

Organization-Aware Data Fetching

export async function getAllProjects() {
  const context = await getCurrentUserOrganization()
  if (!context) {
    return { success: false, error: 'Unauthorized' }
  }
  
  const projects = await db
    .select()
    .from(projectsTable)
    .where(eq(projectsTable.organizationId, context.organization.id))
    .orderBy(desc(projectsTable.createdAt))
  
  return { success: true, projects }
}

🔍 Testing Organization Isolation

Verification Steps

1. Create multiple organizations in the database
2. Assign different users to different organizations
3. Verify data isolation by attempting cross-organization access
4. Test RLS policies by querying directly with different user contexts
5. Validate API endpoints return only organization-scoped data

Test Queries

-- Test RLS policy (should only return user's organization data)
SET ROLE authenticated;
SET request.jwt.claims TO '{"sub": "user-uuid-here"}';
SELECT * FROM projects; -- Should only show user's org projects

-- Test service role bypass
SET ROLE service_role;
SELECT * FROM projects; -- Should show all projects

🚨 Security Considerations

Critical Security Rules

1. Never bypass organization checks in application code
2. Always use organization context in server actions
3. Verify organization membership before data access
4. Use RLS policies as the final security layer
5. Audit organization access regularly

Common Security Pitfalls

• ❌ Forgetting to add organizationId to WHERE clauses
• ❌ Using global queries without organization filtering
• ❌ Not verifying organization membership in API endpoints
• ❌ Bypassing organization context for "convenience"

📈 Performance Considerations

Database Indexes

-- Organization-scoped indexes for performance
CREATE INDEX idx_projects_organization_id ON projects(organization_id);
CREATE INDEX idx_draws_organization_id ON draws(organization_id);
CREATE INDEX idx_documents_organization_id ON documents(organization_id);

-- Composite indexes for common queries
CREATE INDEX idx_projects_org_created_at ON projects(organization_id, created_at DESC);
CREATE INDEX idx_draws_org_status ON draws(organization_id, status);

Query Optimization

• Use organization_id in WHERE clauses early
• Leverage composite indexes for common query patterns
• Consider organization-specific data partitioning for large datasets

🔄 Future Enhancements

Planned Features

1. Organization switching UI for users in multiple organizations
2. Organization-specific settings and customizations
3. Cross-organization collaboration with explicit permissions
4. Organization analytics and usage tracking
5. Automated organization provisioning from domain verification

Scalability Considerations

• Database partitioning by organization for very large datasets
• Separate databases per organization for enterprise customers
• Caching strategies that respect organization boundaries
• Background job isolation by organization

📚 Related Documentation

• Database Schema
• Organization Utilities
• Server Actions
• API Routes

---

⚠️ IMPORTANT: This multi-tenancy implementation is critical for data security. Any changes to organization-related code should be thoroughly reviewed and tested to ensure data isolation is maintained.